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.

Gordon Linoff · Accepted Answer · 2018-01-15 13:15:40Z

5

Just use coalesce():

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

answered Jan 15, 2018 at 13:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jamheadart Over a year ago

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.

sheavens Over a year ago

I was going to suggest a case statement, but this is way more elegant +1

Gavin Over a year ago

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!

Collectives™ on Stack Overflow

Updating from stored procedure but only if variable is not null

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related