Sqlite> SELECT DISTINCT name FROM COMPANY Now, let us use DISTINCT keyword with the above SELECT query and see the result. SELECT DISTINCT column1, lumnNĬonsider COMPANY table with the following records.įirst, let us see how the following SELECT query returns duplicate salary records.
Syntaxįollowing is the basic syntax of DISTINCT keyword to eliminate duplicate records. While fetching such records, it makes more sense to fetch only unique records instead of fetching duplicate records. There may be a situation when you have multiple duplicate records in a table.
#SQLITE INSERT DUPLICATE TO UNIQUE HOW TO#
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.SQLite DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only the unique records.
#SQLITE INSERT DUPLICATE TO UNIQUE CODE#
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. ON DUPLICATE KEY UPDATE c1 = VALUES(c1) + 1 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) If the existing row is updated using its current values, the number of affected rows is 0.If the existing row is updated, the number of affected rows is 2. Unique Indexes If the UNIQUE keyword appears between CREATE and INDEX then duplicate index entries are not allowed.
If the new row is inserted, the number of affected rows is 1.MySQL returns the number of affected rows based on the action it performs: If a duplicate error occurs, it will update the existing row with the value specified in the ON DUPLICATE KEY UPDATE clause. The only addition to the INSERT statement is the ON DUPLICATE KEY UPDATE clause where you specify a list of column-value-pair assignments in case of duplicate.īasically, the statement first tries to insert a new row into the table. Code language: SQL (Structured Query Language) ( sql ) The syntax of INSERT ON DUPLICATE KEY UPDATE statement is as follows: INSERT INTO table (column_list) However, if you specify the ON DUPLICATE KEY UPDATE option in the INSERT statement, MySQL will update the existing row with the new values instead. When you insert a new row into a table if the row causes a duplicate in UNIQUE index or PRIMARY KEY, MySQL will issue an error. The INSERT ON DUPLICATE KEY UPDATE is a MySQL’s extension to the SQL standard’s INSERT statement. Introduction to the MySQL INSERT ON DUPLICATE KEY UPDATE statement Summary: in this tutorial, you will learn how to use MySQL INSERT ON DUPLICATE KEY UPDATE statement to update data if a duplicate in the UNIQUE index or PRIMARY KEY error occurs when you insert a row into a table.
0 kommentar(er)
