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
