Home  »     »     »  How can We do INSERT and UPDATE through a single MySQL Query? - "INSERT ..... ON DUPLICATE KEY UPDATE" Syntax

How can We do INSERT and UPDATE through a single MySQL Query? - "INSERT ..... ON DUPLICATE KEY UPDATE" Syntax

Thursday, May 30, 2013

How can We do INSERT and UPDATE through a single MySQL Query? - "INSERT ..... ON DUPLICATE KEY UPDATE" Syntax

INSERT and UPDATE Actions in one MySQL Statement

Usually Update functions will check for a record with given key and if it exist then function will execute the UPDATE Query else perform an INSERT Query. So here happens a SELECT query on Table Followed by INSERTION / UPDATION of a Record.

You can do this in single MySQL query. How?


the Answer is "ON DUPLICATE KEY UPDATE" Option provided by MySQL from its 4.1.0 version onwards.

INSERT ... ON DUPLICATE KEY UPDATE Syntax

INSERT INTO YOUR_TABLE_NAME( ID, COL1, COL2 ) VALUES ( $id, $col1_val, $col2_val )
ON DUPLICATE KEY UPDATE COL1 = $col1_val, COL2=$col2_val

If you use the clause "ON DUPLICATE KEY UPDATE", and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the existing row is performed.

The ON DUPLICATE KEY UPDATE clause can contain multiple column assignments, separated by commas.
With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, and 2 if an existing row is updated.

References:
MYSQL.com - INSERT ..... ON DUPLICATE KEY UPDATE"
chapter31.com - Query Speed test



How to link to this page?
If you wish to link to this page from your website, simply Copy and paste the above HTML code to your web page. It will appear on your page as:
How can We do INSERT and UPDATE through a single MySQL Query? - "INSERT ..... ON DUPLICATE KEY UPDATE" Syntax.




Share this!




comments powered by Disqus

This Weeks 7 Popular Posts


Subscribe to Recent Posts by Email
Stay connected to CROZOOM with regular Email notices of new Techie articles and IT Jobs. Updates will be delivered to your Inbox as soon as they are posted online.

Enter Your Email Address:  

Delivered by FeedBurner   RSS Feed

Search this Blog   



Urgent Openings for PHP trainees, Andriod / IOS developers and PHP developers in Kochi Trivandrum Calicut and Bangalore. Please Send Your updated resumes to recruit.vo@gmail.com   Read more »