Updating from stored procedure but only if variable is not null

Question

Is there an easy way to do a single update statement in a stored procedure, where we can ignore variables that fit a certain condition?

E.g. general statement would be:

UPDATE XYZ SET a = z, b = y, c = x

but if y was NULL then it would ignore the b = y segment of the statement?

I don't want to us IFs for each variable and then either build and concat a statement or update columns one by one...

I've looked Here and it seemed to be on the right track but I think it's for updating the value if its NULL, not if the variable itself is null?!

I'm using, but not so great with, MySQL. And I don't the above is what I'm after, other searches are proving fruitless.

score 5 · Accepted Answer · answered Jan 15 '18 at 13:15

5

Just use coalesce():

UPDATE XYZ
    SET a = z,
        b = COALESCE(y, b),
        c = x;

answered Jan 15 '18 at 13:15

Gordon Linoff

Yup, thanks, I'll mark this as correct as soon as I can. I've just realised the COALESCE is exactly what I was looking for. I guess the link I provided might mean this is a duplicate question. – jamheadart Jan 15 '18 at 13:20
I was going to suggest a case statement, but this is way more elegant +1 – sheavens Jan 15 '18 at 13:20
Seeing this comment just now has been my "oh, of course, I'd forgotten about that!" moment. I used to use it all the time, managed to completely forget about it, and now think I'll be using it everywhere again. Thank you! – Gavin Mar 03 '23 at 13:03

1 Answers1