Question 21194 in Mysql Question posted in
The official MySQL documentation can be found here.

How can I do an update with case and select? – Mysql

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.

Tags:

2

Answers


  1. 0

    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);
    Login or Signup to reply.
  2. 0

    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>);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search