HI!
I have messed a little with rivendell database, quite successfully, but
only for query/read operations ... altering/writing directly the
database of such a complex application is for the brave :-)
On 7/13/21 10:14 PM, Dave B wrote:
I want to bulk update the scheduler codes for my songs based on the YEAR field,
but I get an error when attempting to add songs to the CART_SCHED_CODES table.
Sample SQL to add my 70s scheduler code to songs is:
Insert into CART_SCHED_CODES (CART_NUMBER, SCHED_CODE) values ((select NUMBER from
CART where YEAR > str_to_date(‘1969’, ‘%Y’) and YEAR < str_to_date(‘1980’,
‘%Y’)), ‘70s’);
At first glance, I bet you can just use LIMIT on the subquery... and the
specific (first) problem you encountered should be bypassed BUT... I
don't think that being a good idea.
At first glance, I see you're trying to insert TWO values into TWO
fields (CART_NUMBER, SCHED_CODE) whereas you're subquerying for just ONE
value (NUMBER) so I bet you'll need an additional field or the DBEngine
may complain on that too.
Then for the subquery itself, consider that If there are more than one
result/row, you should decide which one is the right one... the DBEngine
will never consider itself powerfull enough to impersonate you and take
that decission by you... you have to tell it the criteria for such
decission and the criteria must throw a single row, so you'll never
could blame on the DBEngine for selecting the wrong one :-P
Are all the subquery results equal? well... in that case the LIMIT trick
could be enough, why not, but otherwise, you have to, at least try
GROUPing BY and/or ORDERing BY the subquery results following your
intended logic (and if the logic is complex maybe grouping/ordering
wouldn't be enough... JOINining, etc... hopefully GROUing/ORDERing BY
will be enough)
My advice is that, first, you JUST work on the subquery ( just SELECT)
playing with grouping, ordering, limiting (or whatever be necessary)
until you get just the TWO unequivocal values you want to insert, from a
SINGLE unequivocal result row... then paste that final query as the
subquery of your 'parent' query and it should execute.
Good luck, and best regards!
This throws an error because the sub query returns more than one row. Any
suggestions for correct SQL or a better way of doing this?
Thanks,
Dave
Sent from my iPad
_______________________________________________
Rivendell-dev mailing list
[email protected]
http://caspian.paravelsystems.com/mailman/listinfo/rivendell-dev
_______________________________________________
Rivendell-dev mailing list
[email protected]
http://caspian.paravelsystems.com/mailman/listinfo/rivendell-dev
