CODEWITHSUNDEEP
mysqlsqlstored-procedures
jamheadart
jamheadart

Reputation: 5313

Updating from stored procedure but only if variable is not null

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.

Upvotes: 2

Views: 945

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Just use coalesce():

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

Upvotes: 5

Related Questions