![]() ![]() In this tutorial, you have learned how to insert or update data in a table using the ON DUPLICATE KEY UPDATE option of the INSERT statement. MySQL issues the following message: 2 row(s) affected Code language: SQL (Structured Query Language) ( sql )īecause a row with id 4 already exists in the devices table, the statement updates the name from Printer to Central Printer. ON DUPLICATE KEY UPDATE name = 'Central Printer' Code language: SQL (Structured Query Language) ( sql ) VALUES ( 'Printer') Code language: SQL (Structured Query Language) ( sql )įinally, insert a row with a duplicate value in the id column. The statement above has the same effect as the following statement: INSERT INTO devices( name) ON DUPLICATE KEY UPDATE name = 'Printer' Code language: SQL (Structured Query Language) ( sql )īecause there is no duplicate, MySQL inserts a new row into the devices table. Now, we have three rows in the devices table.Īfter that, insert one more row into the devices table. Then, query the data from the devices table to verify the insert: SELECT id,ĭevices Code language: SQL (Structured Query Language) ( sql ) VALUES( 'Router F1'),( 'Switch 1'),( 'Switch 2') Code language: SQL (Structured Query Language) ( sql ) Next, insert rows into the devices table. ) Code language: SQL (Structured Query Language) ( sql ) ![]() Let’s take a look at an example of using the INSERT ON DUPLICATE KEY UPDATE to understand how it works.įirst, create a table named devices to store the network devices: CREATE TABLE devices ( MySQL INSERT ON DUPLICATE KEY UPDATE example The statement above sets the value of the c1 to its current value specified by the expression VALUES(c1) plus 1 if there is a duplicate in UNIQUE index or PRIMARY KEY. When you insert a new row into a table if the row causes a. ![]() ![]() ON DUPLICATE KEY UPDATE c1 = VALUES(c1) + 1 The INSERT ON DUPLICATE KEY UPDATE is a MySQLs extension to the SQL standards INSERT statement. To use the values from the INSERT clause in the DUPLICATE KEY UPDATE clause, you use the VALUES() function as follows: INSERT INTO table_name(c1)
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |