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 contact me 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)