MKRD.info - Thorough articles on a variety of topics, and no ads!

INSERT INTO ... ON DUPLICATE KEY UPDATE instead of REPLACE INTO

Use INSERT INTO ... ON DUPLICATE KEY UPDATE instead of REPLACE INTO

Switch:

replace into mytable (select * from temptable);

With:

INSERT INTO tablename (uid, fieldtoconvertname) VALUES (uidfromtemptable, convertedfieldname)

ON DUPLICATE KEY UPDATE fieldtoconvertname = VALUES (convertedfieldname);

or, with a Select statement,

INSERT ... SELECT Syntax

INSERT

INTO tbl_name [(col_name,...)]

SELECT ...

ON DUPLICATE KEY UPDATE col_name=expr, ... ;

With INSERT ... SELECT, you can quickly insert many rows into a table from one or many tables. For example:

(one field to insert example)

INSERT INTO tbl_temp2 (fld_id)

SELECT tbl_temp1.fld_order_id

FROM tbl_temp1;

(several fields to insert example)

INSERT INTO tbl_a (a,b,c)

SELECT a,b,c FROM tbl_b

ON DUPLICATE KEY UPDATE c = tbl_b.c;

Example for our conversion:

Keeping with the conventions from the "Getting out of MySQL Character Set Hell" article, the command:

replace into mytable (select * from temptable);

can be modified into a safer and MySQL 5.0 & 5.1 -working command:

INSERT INTO mytable

SELECT * FROM temptable

ON DUPLICATE KEY UPDATE myfield = temptable.myfield;

˅˅˅ Additional valuable information is available at one of the links below: ˅˅˅