Re: Combining SQL Updates

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 01/29/2018
05:38:31 PM:

Case statement?


On Mon, Jan 29, 2018 at 2:31 PM, <dlclark@xxxxxxxxxxxxxxxx> wrote:

I have a curiosity question that hopefully might also teach me
more about SQL set-based processing. We added a foreign key to an
existing table. To initialize the new column, we ran the following

two

updated statements. My question is whether there is a way to do this

as a

single update set -- rather than processing the table twice?


Update SOOSH01T
Set SOWFSTSTID = (Select SOWFSTSTID from SOWFSTST
Where WRKFLWCDE = 'DELIVERED')
Where SOOSH01TID in (Select SOOSH01TID from SOOSH00V03
Where SHPSTSCDE = 'Closed');

Update SOOSH01T
Set SOWFSTSTID = (Select SOWFSTSTID from SOWFSTST
Where WRKFLWCDE = 'WAREHOUSEREADY')
Where SOOSH01TID in (Select SOOSH01TID from SOOSH00V03
Where SHPSTSCDE <> 'Closed');

I thought of a CASE statement in the SET clause but how would I
make that specific to the WHERE for the second subselect? Meaning, how
can I tell if the SET is for an OPEN order or for a CLOSED order?


Sincerely,

Dave Clark