Connor Bishop Connor Bishop - 17 days ago 5
MySQL Question

SQL - Update multiple records in one query with Composite Key

I have looked at this question which addresses updating multiple records in one query.

The general solution is

UPDATE table_name
SET field_to_update = CASE table_key
WHEN key_value1 THEN field_value1
WHEN key_value2 THEN feild_value2
ELSE feild_to_update
END
WHERE table_key IN(key_value1 , key_value2);


My question is who can this be adapted to cater for a composite key. Say if I have columns

(id_1, id_2, column_to_update)


where id_1 and id_2 form a composite primary key.

My problem is made simpler by the fact that one of the id columns will be constant for a particular query.

For example, I need something along the lines of

UPDATE table_name
SET field_to_update = CASE (key1, key2)
WHEN (1,1) THEN field_value1
WHEN (2,1) THEN feild_value2
ELSE feild_to_update
END
WHERE (key1, key2) IN ( (1, 1) , (2, 1) );


Can anyone help please?

Answer

The use of tuple in case is not allowed case allow only one operand if you use a tuple like in your case you have the error "Operand should contain 1 column(s)" because are 2 operands

but you can override with some manipulation eg a concat (and implicit conversion )

  UPDATE table_name
   SET field_to_update = CASE concat(key1, key2) 
                WHEN concat(1,1) THEN field_value1 
                WHEN concat(2,1) THEN feild_value2 
                ELSE feild_to_update
                END
   WHERE concat(key1, key2) IN ( concat(1, 1) , concat(2, 1) );