Mercer Mercer - 6 months ago 8
SQL Question

Do an update without knowing the ID

I have this request:

UPDATE PEOPLE SET ID_STATE = 5 WHERE ID_STATE IN (3,4);


I would make the same request but if I do not know the
ID_STATE
but just the
labelStatePeople


SELECT ID_STATE FROM STATE_PEOPLE WHERE labelStatePeople = 'blue';


EXAMPLE:

$1 = SELECT ID_STATE FROM STATE_PEOPLE WHERE labelStatePeople = 'blue';
$2 = SELECT ID_STATE FROM STATE_PEOPLE WHERE labelStatePeople = 'yellow';
$3 = SELECT ID_STATE FROM STATE_PEOPLE WHERE labelStatePeople = 'red';


final request:
UPDATE PEOPLE SET ID_STATE = $1 WHERE ID_STATE IN ($2,$3);

Answer

Simply have a sub-query that returns those strange people's ID_STATE values:

UPDATE PEOPLE
  SET ID_STATE = 5
WHERE ID_STATE IN (SELECT ID_STATE FROM STATE_PEOPLE
                   WHERE labelStatePeople = 'strange'
                    and ID_STATE IS NOT NULL)

(The sub-query's ID_STATE IS NOT NULL part can be removed if that column is NOT NULL.)

Comments