sql - Update a table field where there are multiple tables -
i trying update table (locations) set field "no_ship" 'y' when following conditions met. please , thank you!
here code far:
update locations l set l.no_ship='y' arinvt.id = fgmulti.arinvt_id , fgmulti.loc_id = l.id , ((fgmulti.lot_date + arinvt.shelf_life) - sysdate) / arinvt.shelf_life < .8 , l.loc_desc <> 'nc0101' , arinvt.class = 'fg' , l.id <> 27051
you can't join within update
itself, never mind introduce references other tables @ will. need use correlated subquery identify rows want update. like:
update locations l set l.no_ship = 'y' l.loc_desc <> 'nc0101' , l.id <> 27051 , ( select min(((f.lot_date + a.shelf_life) - sysdate) / a.shelf_life) fgmulti f join arinvt on a.id = f.arinvt_id f.loc_id = l.id , a.class = 'fg' ) < .8
the correlation subquery refers l.id
outer update statement.
completely untested of course, since don't have table definitions or sample data. point in right direction though. (edited add aggregate function in subquery returns 1 row).
or check existence of matching row in subquery:
update locations l set l.no_ship = 'y' l.loc_desc <> 'nc0101' , l.id <> 27051 , exists ( select null fgmulti f join arinvt on a.id = f.arinvt_id f.loc_id = l.id , a.class = 'fg' , ((f.lot_date + a.shelf_life) - sysdate) / a.shelf_life < .8 );
you have subquery include own copy of locations
joined other 2 tables, finding relevant ids; , have update use in
clause update rows matching ids. might want check performance of each approach.
based on comment want update fgmulti
, , since there isn't obvious direct link between arinvt
, locations
you've shown, use method this:
update fgmulti set no_ship = 'y' loc_id in ( select f.loc_id fgmulti f join arinvt on a.id = f.arinvt_id join locations l on l.id = f.loc_id l.loc_desc <> 'nc0101' , l.id <> 27051 , a.class = 'fg' , ((f.lot_date + a.shelf_life) - sysdate) / a.shelf_life < .8 );
the subquery can run on own find rows need updated; in
applies update those. still don't have table structures or relationships i'm going columns mentioned; means fgmulti
rows loc_id
found subquery updated. may broad. if fgmulti
has it's own primary key, use instead:
update fgmulti set no_ship = 'y' id in ( select f.id ...
Comments
Post a Comment