更新插入时的旧列值

我有这样的桌子

|  id_1  |  id_2  |  new  |
|  1     |  1     |  1    |
|  1     |  2     |  1    |
|  1     |  3     |  1    |

now i want to add more rows and update old rows having unique key on [id_1, id_2] so i do this queries

UPDATE table
SET new = 0
WHERE id_1 = 1
---
INSERT INTO table (id_1, id_2, new) 
VALUES (1, 1, 1), (1, 3, 1), (1, 4, 1)
ON DUPLICATE KEY UPDATE new = 1

so i get this table because i didn't update id_2 = 2 row

|  id_1  |  id_2  |  new  |
|  1     |  1     |  1    |
|  1     |  2     |  0    |
|  1     |  3     |  1    |
|  1     |  4     |  1    |

我如何才能更有效地做到这一点,也许像一个查询

INSERT INTO table (id_1, id_2, new) 
VALUES (1, 1, 1), (1, 3, 1), (1, 4, 1)
ON DUPLICATE KEY 
UPDATE (new = 0 WHERE id_1 = 1) AND UPDATE (new = 1)
评论