Search code examples
mysqlsql

How to write a conditional UPDATE statement in SQL

My Table has these columns

id_+owner_id+security_owner_id+_developer_id

and these ids are user ids so one user can have all the roles

I want to set these columns to null if user is removed;

something like

row-1: 1,2,2,2 and user_id=2 removed then updated row would be 1,null,null,null

row-2: 1,2,3,7 and user_id=2 removed then updated row would be 1,null,3,7

Solution

  • You can use conditional logic:

    update mytable
    set owner_id = nullif(owner_id, 2),
        security_owner_id = nullif(security_owner_id, 2),
        developer_id = nullif(developer_id, 2)
    where 2 in (owner_id, security_owner_id, developer_id);

    You can put this logic either in a trigger or stored procedure or in your application.