How can I do an update with case and select?-CodePudding

I'm trying to assign an ID code by searching for the name.

update db.request r
set s.CreateByID = ( 
                select r.ID 
                from db.user u
                where r.NameCreateBy = u.Name
                )
where r.ID in (23506);

How would I put a case clause for when I can't find the name in the user table?

The request table has the correct name of the person who created the request. But the createByID column is returning a code referring to another user. So I need to update createByID based on the user ID, disregarding the value shown in the request table.

CodePudding user response：

you can use COALESCE if the subquery returns NULL amd set a value for example 0

update db.request r
set r.CreateByID = COALESCE(( 
                select u.ID 
                from db.user u
                where r.NameCreateBy = u.Name
                ),0)
where r.ID in (23506);

CodePudding user response：

Consider using a JOIN inside the UPDATE statement, if you need to match fields between two tables:

UPDATE     db.request r
INNER JOIN db.user    u ON r.NameCreateBy = u.Name AND r.ID IN (23506)
SET s.CreateByID = COALESCE(r.ID, <substitution_value>);