Mysql using coalece within update statement
How would I use coalesce within an update statement. All example I have seen use select statement.
UPDATE d.n n JOIN d.o o ON n.ID = o.ID SET n.OC = o.OC
开发者_高级运维
need n.oc to Coalesce( n.oc,0)
but Sql failed
CALL SQLExecute(CONCAT('UPDATE d.n n JOIN data.o_ o ON n.id = o.id SET COALESCE(n.OC, null) = o.OC' ));
Your query UPDATE d.n n JOIN data.o_ o ON n.id = o.id SET COALESCE(n.OC, null) = o.OC
does not make sense. COALESCE(x,y)
uses x if x is not null, otherwise it uses y.
So your query should be
UPDATE d.n n JOIN data.o_ o ON n.id = o.id SET n.OC = o.OC //if null allowed
or this
UPDATE d.n n JOIN data.o_ o ON n.id = o.id SET n.OC = COALESCE(o.OC,'somevalue') //if null not allowed
Oh, my. You CAN'T update function!
SET n.OC = COALESCE(o.OC, 0)
at least
精彩评论