When inserting via a select I don't think you need to use a subquery or values statement.. see: https://stackoverflow.com/questions/9422529/mysql-how-do-you-insert-into-a-table-with-a-select-subquery-returning-multiple-r
insert into CART_SCHED_CODES (CART_NUMBER, SCHED_CODE) select NUMBER, '70s' from CART where YEAR > str_to_date('1969', '%Y) and YEAR < str_to_date('1980', '%Y') Of course, backup your database first :) Jarret On Wed, Jul 14, 2021 at 5:14 AM Alejandro olivan Alvarez < alejandro.olivan.alva...@gmail.com> wrote: > 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 > > Rivendell-dev@lists.rivendellaudio.org > > http://caspian.paravelsystems.com/mailman/listinfo/rivendell-dev > _______________________________________________ > Rivendell-dev mailing list > Rivendell-dev@lists.rivendellaudio.org > http://caspian.paravelsystems.com/mailman/listinfo/rivendell-dev >
_______________________________________________ Rivendell-dev mailing list Rivendell-dev@lists.rivendellaudio.org http://caspian.paravelsystems.com/mailman/listinfo/rivendell-dev