INSERT ... ON DUPLICATE KEY UPDATE

原创
2016/08/18 21:06
阅读数 84

INSERT ... ON DUPLICATE KEY UPDATE

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have similar effect:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;
UPDATE table SET c=c+1 WHERE a=1;

(The effects are not identical for an InnoDB table where a is an auto-increment column. With an auto-increment column, an INSERT statement increases the auto-increment value but UPDATE does not.)

The ON DUPLICATE KEY UPDATE clause can contain multiple column assignments, separated by commas. for example ,

 INSERT INTO t_vehicle
        (vehicle_id,
        model_id,
        vehicle_no,
        frame_no,
        engine_no
      )
	VALUES
	(1, 2522, '456', '789', '012') ON DUPLICATE KEY UPDATE 
        vehicle_id = 1 , frame_no = '123' , vehicle_no = '323';

如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部