SQL - Update multiple records in one query with Composite Key

Question

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?

case allow only on operand if you use a tuple like in your case you have the error "Operand should contain 1 column(s)" because are 2 operands — ScaisEdge, Nov 22 '16 at 19:24
yeah I know but I just wanted to get across what I want to be able to do. Even it requires totally different syntax I don't mind. — Connor Bishop, Nov 22 '16 at 19:26
There is an alternate form of `CASE`; `CASE WHEN [condition] THEN [blah] WHEN [condition2] THEN [blah2] .... ELSE .... END`. — Uueerdo, Nov 22 '16 at 19:27

score 2 · Accepted Answer · answered Nov 22 '16 at 19:27

2

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) );

answered Nov 22 '16 at 19:27

ScaisEdge

131,976
10
91
107

@ConnorBishop . thanks be sure of use the correct manipolation for avoid false positive (two different value that join became same) – ScaisEdge Nov 22 '16 at 19:34
@scaisEdge a good way to do that is usually just adding an unlikely string between the two values; for example, in the case of numbers `CASE CONCAT(key1, '|', key2) WHEN '1|1' THEN ...` – Uueerdo Nov 22 '16 at 20:06

score 0 · Answer 2 · answered Mar 03 '23 at 12:50

Another way

UPDATE `table_name`                                                             
SET                                                                
       `desc`   = CASE                                    
                    WHEN (`id_one`='1' AND `id_tow`='1') THEN '22222'                                                               
                    END                                                          
WHERE  (`id_one`, `id_tow`) IN ( ('1', '1') );

SQL - Update multiple records in one query with Composite Key

2 Answers2

Linked