0

I want to update multiple rows identified by unique key without inserting new rows.

Below is my table:

CREATE TABLE `insert_update_ignore` (
   `obj_id` int(11) NOT NULL,  
   `obj_type` tinyint(4) NOT NULL,
   `value` int(11) DEFAULT '-1',  
   UNIQUE KEY `unique_key` (`obj_id`,`obj_type`)) 
ENGINE=InnoDB DEFAULT  CHARSET=utf8

The table has some existing records:

mysql> select * from insert_update_ignore;
+--------+----------+-------+
| obj_id | obj_type | value |
+--------+----------+-------+
|      1 |        1 |    -1 |
|      1 |        2 |    -1 |
|      2 |        1 |    -1 |
|      2 |        2 |    -1 |
+--------+----------+-------+

I have some values to update the value column in the table. 

INSERT INTO insert_update_ignore(obj_id, obj_type, value)
    VALUES(1, 1, 1),
          (1, 2, 3),
          (2, 1, 1),
          (2, 2, 5),
          (3, 1, 10)
    ON DUPLICATE KEY UPDATE
       value = VALUES(value);

This almost done the job except that the last value, i.e. (3, 1, 10) is inserted into the table. This one should not be inserted because the unique key (3, 1) do not exists before.

How can i just do the UPDATE part, but not INSERT? The table schema can be changed if better implementation exists. The values to be update are calculated from other database(running on different machine and different port).

I dig out the one below do the job, but the statement is too big if the values are in thousands. Any more elegant ways to do this? Thanks if any one can help on this. P.S I am coding this in Python and using the MySQLdb library.

UPDATE insert_update_ignore
SET value = CASE
    WHEN (obj_id = 1 AND obj_type = 1) THEN 1
    WHEN (obj_id = 1 AND obj_type = 2) THEN 3
    WHEN (obj_id = 2 AND obj_type = 1) THEN 1
    WHEN (obj_id = 2 AND obj_type = 2) THEN 5
    WHEN (obj_id = 3 AND obj_type = 1) THEN 10
    ELSE value
END 
WHERE (obj_id, obj_type) IN ((1, 1), (1,2), (2, 1), (2, 2), (3, 1));
schemacs
  • 2,783
  • 8
  • 35
  • 53

2 Answers2

1

You can put all the values you wish to update to into a temporary table first. Then update all the values in your original table from the temporary table if they can be joined.

create temporary table temp like insert_update_ignore;

insert into temp
    values(1, 1, 1),
          (1, 2, 3),
          (2, 1, 1),
          (2, 2, 5),
          (3, 1, 10);

update insert_update_ignore i
 join temp t
 using (obj_id, obj_type)
 set i.value = t.value;

drop temporary table temp;

I've put all this into a SQLFiddle as well.

Andreas Wederbrand
  • 38,065
  • 11
  • 68
  • 78
0

The entire purpose of INSERT is to insert new rows, so of course it's going to insert a new row (if a duplicate key is not found; otherwise, update it). If you only want to update existing rows then use UPDATE queries.

Rather than use CASE ... WHEN ... THEN ... ELSE ... END iterate over each unique key and UPDATE ... WHERE ....

Edit:

For example:

UPDATE `insert_update_ignore` SET `value` = 1 WHERE `obj_id` = 1 AND `obj_type` = 1;
UPDATE `insert_update_ignore` SET `value` = 3 WHERE `obj_id` = 1 AND `obj_type` = 2;
UPDATE `insert_update_ignore` SET `value` = 1 WHERE `obj_id` = 2 AND `obj_type` = 1;
UPDATE `insert_update_ignore` SET `value` = 5 WHERE `obj_id` = 2 AND `obj_type` = 2;
UPDATE `insert_update_ignore` SET `value` = 10 WHERE `obj_id` = 3 AND `obj_type` = 1;

The first 4 queries will update the rows which match the criteria in the WHERE clause whereas the last will not as no row satisfies the conditions.

Michael
  • 11,912
  • 6
  • 49
  • 64