RE: Combining SQL Updates

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

Try this, it might work but obviously I cannot test it on my system.

Update SOOSH01T O
Set SOWFSTSTID = case when (Select 1 from SOOSH00V03 I Where
O.SOOSH01TID = I.SOOSH01TID and SHPSTSCDE = 'Closed') = 1 then
(Select SOWFSTSTID from SOWFSTST Where WRKFLWCDE = 'DELIVERED')
when (Select 1 from SOOSH00V03 I Where
O.SOOSH01TID = I.SOOSH01TID and SHPSTSCDE <> 'Closed') = 1 then
(Select SOWFSTSTID from SOWFSTST Where WRKFLWCDE = 'WAREHOUSEREADY')
else SOWFSTSTID end

Thanks. I did get that to work (see after statistics). But I
thought I'd check with Visual Explain and, with two runs of each
statement, here are the results from the second run of each statement.
So, what do y'all think? Better to keep it simple even with more than one
pass of the file? Or, better to be more complex as long as you can do it
all in one statement?


Visual Explain Closed Not Closed Totaled AllInOne
---------------------- ------ ---------- ------- --------
Optimization Time (ms) 958 919 1,877 1,881
Run Time (ms) 380 231 611 882
Rows Fetched 1,014 191 1,205 1,205
CPU Time 8 3 11 18


Update SOOSH01T as Mst
Set Mst.SOWFSTSTID =
Case When (Select 1 from SOOSH00V03 as Sub
Where Sub.SOOSH01TID = Mst.SOOSH01TID
and Sub.SHPSTSCDE = 'Closed') = 1
Then (Select Lku.SOWFSTSTID from SOWFSTST as Lku
Where Lku.WRKFLWCDE = 'DELIVERED')
When (Select 1 from SOOSH00V03 as Sub
Where Sub.SOOSH01TID = Mst.SOOSH01TID
and Sub.SHPSTSCDE <> 'Closed') = 1
Then (Select Lku.SOWFSTSTID from SOWFSTST as Lku
Where Lku.WRKFLWCDE = 'WAREHOUSEREADY')
Else Mst.SOWFSTSTID
End
With NC;


Sincerely,

Dave Clark