running multiple updates at same time

[yossik]

Hi



I have a multiple (over 300!) 'Update XXX set YYY' commands to run . all for the same table and field, but with different criteria. something like
update XXX set Y= a where W=3 and Z =4;
update XXX set Y= b where W=5 and Z =6;
update XXX set Y= a where W=8 and Z =10;

I can run them separately. But is there a way to run them at one time?

[pbaldy]

2 thoughts, either putting those three fields in a table you can join on or creating a function that accepts the W and Z values and returns the Y.

[Dal Jeanis]

Here's the demo for pabldy's first option -

With a table of new values laid out like this -

tblNewVal
WVal ZVal NewY
3     4     a
5     6     b
8    10     a

You can have a single update like this -

UPDATE MyTable AS T1, tblNewVal AS TN
SET T1.Y = TN.NewY
WHERE T1.W = TN.WVal
AND T1.Z = TN.ZVal;

But first check for dups in your conditions with something like this -

SELECT WVal, ZVal, Count(NewY)
FROM tblNewVal
GROUP BY Wval, ZVal
HAVING Count(NewY) > 1;

And check for unmet/unset conditions like this -

SELECT T1.W, T1.Z
FROM MyTable AS T1 LEFT JOIN tblNewVal AS TN 
ON T1.W = TN.WVal AND T1.Z = TN.ZVal
WHERE TN.NewY Is Null;

[pbaldy]

You're not going to write the function too?

[Dal Jeanis]

Nah, gotta leave some things to the novices...