on duplicate key oracle,INSERT … ON DUPLICATE KEY UPDATE

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。