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: ˅˅˅

 

Did you like the article? Let Google Search know by clicking this button: . Please link to content that you find useful on this website on your own website, forum or blog! You can also comment on this page below, or to ask a question or suggest a topic for me to research. There is a user-editable Wiki available on my website, as well as a Forum that you can contribute to. Site Map.

Page last modified 31-Dec-12 22:04:25 EST
Comments on this page:


Add a comment to this page
Comment Title:
Your Name:
Your Email Address:
Notify me of new comments to this item:
Additional Comments:
This is a captcha-picture. It is used to prevent mass-access by robots. (see: www.captcha.net)