Mysql duplicate

Mysql duplicate DEFAULT

INSERT ON DUPLICATE KEY UPDATE

Syntax

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_list)] [(col,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col=expr [, col=expr] ... ]

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_list)] SET col={expr | DEFAULT}, ... [ ON DUPLICATE KEY UPDATE col=expr [, col=expr] ... ]

Or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_list)] [(col,...)] SELECT ... [ ON DUPLICATE KEY UPDATE col=expr [, col=expr] ... ]

Description

INSERT ... ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE.

The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the API's flag is set.

If more than one unique index is matched, only the first is updated. It is not recommended to use this statement on tables with more than one unique index.

If the table has an primary key and the statement inserts or updates a row, the function returns its AUTO_INCREMENT value.

The function can only be used in a clause and has no meaning in any other context. It returns the column values from the portion of the statement. This function is particularly useful for multi-rows inserts.

The and options are ignored when you use .

This statement activates INSERT and UPDATE triggers. See Trigger Overview for details.

See also a similar statement, REPLACE.

Examples

CREATE TABLE ins_duplicate (id INT PRIMARY KEY, animal VARCHAR(30)); INSERT INTO ins_duplicate VALUES (1,'Aardvark'), (2,'Cheetah'), (3,'Zebra');

If there is no existing key, the statement runs as a regular INSERT:

INSERT INTO ins_duplicate VALUES (4,'Gorilla') ON DUPLICATE KEY UPDATE animal='Gorilla'; Query OK, 1 row affected (0.07 sec) SELECT * FROM ins_duplicate; +----+----------+ | id | animal | +----+----------+ | 1 | Aardvark | | 2 | Cheetah | | 3 | Zebra | | 4 | Gorilla | +----+----------+

A regular INSERT with a primary key value of 1 will fail, due to the existing key:

INSERT INTO ins_duplicate VALUES (1,'Antelope'); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

However, we can use an INSERT ON DUPLICATE KEY UPDATE instead:

INSERT INTO ins_duplicate VALUES (1,'Antelope') ON DUPLICATE KEY UPDATE animal='Antelope'; Query OK, 2 rows affected (0.09 sec)

Note that there are two rows reported as affected, but this refers only to the UPDATE.

SELECT * FROM ins_duplicate; +----+----------+ | id | animal | +----+----------+ | 1 | Antelope | | 2 | Cheetah | | 3 | Zebra | | 4 | Gorilla | +----+----------+

Adding a second unique column:

ALTER TABLE ins_duplicate ADD id2 INT; UPDATE ins_duplicate SET id2=id+10; ALTER TABLE ins_duplicate ADD UNIQUE KEY(id2);

Where two rows match the unique keys match, only the first is updated. This can be unsafe and is not recommended unless you are certain what you are doing. Note that the warning shown below appears in MariaDB 5.5 and before, but has been removed in MariaDB 10.0, as MariaDB now assumes that the keys are checked in order, as shown in SHOW CREATE TABLE.

INSERT INTO ins_duplicate VALUES (2,'Lion',13) ON DUPLICATE KEY UPDATE animal='Lion'; Query OK, 2 rows affected, 1 warning (0.06 sec) SHOW WARNINGS; +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe | +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ SELECT * FROM ins_duplicate; +----+----------+------+ | id | animal | id2 | +----+----------+------+ | 1 | Antelope | 11 | | 2 | Lion | 12 | | 3 | Zebra | 13 | | 4 | Gorilla | 14 | +----+----------+------+

Although the third row with an id of 3 has an id2 of 13, which also matched, it was not updated.

Changing id to an auto_increment field. If a new row is added, the auto_increment is moved forward. If the row is updated, it remains the same.

ALTER TABLE `ins_duplicate` CHANGE `id` `id` INT( 11 ) NOT NULL AUTO_INCREMENT; ALTER TABLE ins_duplicate DROP id2; SELECT Auto_increment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='ins_duplicate'; +----------------+ | Auto_increment | +----------------+ | 5 | +----------------+ INSERT INTO ins_duplicate VALUES (2,'Leopard') ON DUPLICATE KEY UPDATE animal='Leopard'; Query OK, 2 rows affected (0.00 sec) SELECT Auto_increment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='ins_duplicate'; +----------------+ | Auto_increment | +----------------+ | 5 | +----------------+ INSERT INTO ins_duplicate VALUES (5,'Wild Dog') ON DUPLICATE KEY UPDATE animal='Wild Dog'; Query OK, 1 row affected (0.09 sec) SELECT * FROM ins_duplicate; +----+----------+ | id | animal | +----+----------+ | 1 | Antelope | | 2 | Leopard | | 3 | Zebra | | 4 | Gorilla | | 5 | Wild Dog | +----+----------+ SELECT Auto_increment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='ins_duplicate'; +----------------+ | Auto_increment | +----------------+ | 6 | +----------------+

Refering to column values from the INSERT portion of the statement:

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

See the VALUES() function for more.

See Also

Comments

Sours: https://mariadb.com/kb/en/insert-on-duplicate-key-update/

13.2.5.2 INSERT ... ON DUPLICATE KEY UPDATE Statement

If you specify an clause and a row to be inserted would cause a duplicate value in a index or , an of the old row occurs. For example, if column is declared as and contains the value , the following two statements have similar effect:

The effects are not quite identical: For an table where is an auto-increment column, the statement increases the auto-increment value but the does not.

If column is also unique, the is equivalent to this statement instead:

If matches several rows, only one row is updated. In general, you should try to avoid using an clause on tables with multiple unique indexes.

With , the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the flag to the C API function when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

If a table contains an column and inserts or updates a row, the function returns the value.

The clause can contain multiple column assignments, separated by commas.

In assignment value expressions in the clause, you can use the function to refer to column values from the portion of the statement. In other words, in the clause refers to the value of that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The function is meaningful only in the clause or statements and returns otherwise. Example:

That statement is identical to the following two statements:

For statements, these rules apply regarding acceptable forms of query expressions that you can refer to in an clause:

  • References to columns from queries on a single table, which may be a derived table.

  • References to columns from queries on a join over multiple tables.

  • References to columns from queries.

  • References to columns in other tables, as long as the does not use . One side effect is that you must qualify references to nonunique column names.

References to columns from a do not work reliably. To work around this restriction, rewrite the as a derived table so that its rows can be treated as a single-table result set. For example, this statement can produce incorrect results:

Instead, use an equivalent statement that rewrites the as a derived table:

The technique of rewriting a query as a derived table also enables references to columns from queries.

Because the results of statements depend on the ordering of rows from the and this order cannot always be guaranteed, it is possible when logging statements for the source and the replica to diverge. Thus, statements are flagged as unsafe for statement-based replication. Such statements produce a warning in the error log when using statement-based mode and are written to the binary log using the row-based format when using mode. An statement against a table having more than one unique or primary key is also marked as unsafe. (Bug #11765650, Bug #58637)

See also Section 16.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.

An on a partitioned table using a storage engine such as that employs table-level locks locks any partitions of the table in which a partitioning key column is updated. (This does not occur with tables using storage engines such as that employ row-level locking.) For more information, see Section 22.6.4, “Partitioning and Locking”.


Sours: https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
  1. Bathroom colors pictures
  2. Tuesday bible blessings
  3. New macbook 2017
  4. Rabbitmq maven
  5. Fuck huawei

Summary: in this tutorial, you will learn how to use MySQL statement to update data if a duplicate in the  index or  error occurs when you insert a row into a table.

Introduction to the MySQL INSERT ON DUPLICATE KEY UPDATE statement

The is a MySQL’s extension to the SQL standard’s  statement.

When you insert a new row into a table if the row causes a duplicate in  index or , MySQL will issue an error.

However, if you specify the option in the statement, MySQL will update the existing row with the new values instead.

The syntax of statement is as follows:

Code language:SQL (Structured Query Language)(sql)

The only addition to the statement is the clause where you specify a list of column-value-pair assignments in case of duplicate.

Basically, the statement first tries to insert a new row into the table. If a duplicate error occurs, it will update the existing row with the value specified in the clause.

MySQL returns the number of affected rows based on the action it performs:

  • If the new row is inserted, the number of affected rows is 1.
  • If the existing row is updated, the number of affected rows is 2.
  • If the existing row is updated using its current values, the number of affected rows is 0.

To use the values from the clause in the clause, you use the  function as follows:

The statement above sets the value of the  to its current value specified by the expression  plus 1 if there is a duplicate in  index or .

MySQL INSERT ON DUPLICATE KEY UPDATE example

Let’s take a look at an example of using the  to understand how it works.

First, create a table named to store the network devices:

Code language:SQL (Structured Query Language)(sql)

Next, insert rows into the table.

Code language:SQL (Structured Query Language)(sql)

Then, query the data from the table to verify the insert:

Code language:SQL (Structured Query Language)(sql)
MySQL Insert on duplicate key update example

Now, we have three rows in the table.

After that, insert one more row into the table.

Code language:SQL (Structured Query Language)(sql)
MySQL Insert or Update

Because there is no duplicate, MySQL inserts a new row into the table. The statement above has the same effect as the following statement:

Code language:SQL (Structured Query Language)(sql)

Finally, insert a row with a duplicate value in the column.

Code language:SQL (Structured Query Language)(sql)

MySQL issues the following message:

Code language:SQL (Structured Query Language)(sql)

Because a row with id 4 already exists in the table, the statement updates the name from to .

MySQL INSERT ON DUPLICATE KEY UPDATE - update example

In this tutorial, you have learned how to insert or update data in a table using the option of the statement.

Sours: https://www.mysqltutorial.org/mysql-insert-or-update-on-duplicate-key-update/
SQL- INSERT ON DUPLICATE KEY UPDATE

13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Statement

If you specify an clause and a row to be inserted would cause a duplicate value in a index or , an of the old row occurs. For example, if column is declared as and contains the value , the following two statements have similar effect:

The effects are not quite identical: For an table where is an auto-increment column, the statement increases the auto-increment value but the does not.

If column is also unique, the is equivalent to this statement instead:

If matches several rows, only one row is updated. In general, you should try to avoid using an clause on tables with multiple unique indexes.

With , the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the flag to the C API function when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

If a table contains an column and inserts or updates a row, the function returns the value.

The clause can contain multiple column assignments, separated by commas.

In assignment value expressions in the clause, you can use the function to refer to column values from the portion of the statement. In other words, in the clause refers to the value of that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The function is meaningful only in the clause or statements and returns otherwise. Example:

That statement is identical to the following two statements:

Note

The use of to refer to the new row and columns is deprecated beginning with MySQL 8.0.20, and is subject to removal in a future version of MySQL. Instead, use row and column aliases, as described in the next few paragraphs of this section.

Beginning with MySQL 8.0.19, it is possible to use an alias for the row, with, optionally, one or more of its columns to be inserted, following the or clause, and preceded by the keyword. Using the row alias , the statement shown previously using to access the new column values can be written in the form shown here:

If, in addition, you use the column aliases , , and , you can omit the row alias in the assignment clause and write the same statement like this:

When using column aliases in this fashion, you must still use a row alias following the clause, even if you do not make direct use of it in the assignment clause.

Beginning with MySQL 8.0.20, an INSERT ... SELECT ... ON DUPLICATE KEY UPDATE statement that uses VALUES() in the UPDATE clause, like this one, throws a warning:

You can eliminate such warnings by using a subquery instead, like this:

You can also use row and column aliases with a clause, as mentioned previously. Employing instead of in the two statements just shown can be done as shown here:

The row alias must not be the same as the name of the table. If column aliases are not used, or if they are the same as the column names, they must be distinguished using the row alias in the clause. Column aliases must be unique with regard to the row alias to which they apply (that is, no column aliases referring to columns of the same row may be the same).

For statements, these rules apply regarding acceptable forms of query expressions that you can refer to in an clause:

  • References to columns from queries on a single table, which may be a derived table.

  • References to columns from queries on a join over multiple tables.

  • References to columns from queries.

  • References to columns in other tables, as long as the does not use . One side effect is that you must qualify references to nonunique column names.

References to columns from a are not supported. To work around this restriction, rewrite the as a derived table so that its rows can be treated as a single-table result set. For example, this statement produces an error:

Instead, use an equivalent statement that rewrites the as a derived table:

The technique of rewriting a query as a derived table also enables references to columns from queries.

Because the results of statements depend on the ordering of rows from the and this order cannot always be guaranteed, it is possible when logging statements for the source and the replica to diverge. Thus, statements are flagged as unsafe for statement-based replication. Such statements produce a warning in the error log when using statement-based mode and are written to the binary log using the row-based format when using mode. An statement against a table having more than one unique or primary key is also marked as unsafe. (Bug #11765650, Bug #58637)

See also Section 17.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.


Sours: https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

Duplicate mysql

MySQL - Handling Duplicates



Generally, tables or result sets sometimes contain duplicate records. Most of the times it is allowed but sometimes it is required to stop duplicate records. It is required to identify duplicate records and remove them from the table. This chapter will describe how to prevent the occurrence of duplicate records in a table and how to remove the already existing duplicate records.

Preventing Duplicates from Occurring in a Table

You can use a PRIMARY KEY or a UNIQUE Index on a table with the appropriate fields to stop duplicate records.

Let us take an example – The following table contains no such index or primary key, so it would allow duplicate records for first_name and last_name.

CREATE TABLE person_tbl ( first_name CHAR(20), last_name CHAR(20), sex CHAR(10) );

To prevent multiple records with the same first and last name values from being created in this table, add a PRIMARY KEY to its definition. When you do this, it is also necessary to declare the indexed columns to be NOT NULL, because a PRIMARY KEY does not allow NULL values −

CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), PRIMARY KEY (last_name, first_name) );

The presence of a unique index in a table normally causes an error to occur if you insert a record into the table that duplicates an existing record in the column or columns that define the index.

Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.

The following example does not error out and at the same time it will not insert duplicate records as well.

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) -> VALUES( 'Jay', 'Thomas'); Query OK, 1 row affected (0.00 sec) mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) -> VALUES( 'Jay', 'Thomas'); Query OK, 0 rows affected (0.00 sec)

Use the REPLACE command rather than the INSERT command. If the record is new, it is inserted just as with INSERT. If it is a duplicate, the new record replaces the old one.

mysql> REPLACE INTO person_tbl (last_name, first_name) -> VALUES( 'Ajay', 'Kumar'); Query OK, 1 row affected (0.00 sec) mysql> REPLACE INTO person_tbl (last_name, first_name) -> VALUES( 'Ajay', 'Kumar'); Query OK, 2 rows affected (0.00 sec)

The INSERT IGNORE and REPLACE commands should be chosen as per the duplicate-handling behavior you want to effect. The INSERT IGNORE command keeps the first set of the duplicated records and discards the remaining. The REPLACE command keeps the last set of duplicates and erases out any earlier ones.

Another way to enforce uniqueness is to add a UNIQUE index rather than a PRIMARY KEY to a table.

CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10) UNIQUE (last_name, first_name) );

Counting and Identifying Duplicates

Following is the query to count duplicate records with first_name and last_name in a table.

mysql> SELECT COUNT(*) as repetitions, last_name, first_name -> FROM person_tbl -> GROUP BY last_name, first_name -> HAVING repetitions > 1;

This query will return a list of all the duplicate records in the person_tbl table. In general, to identify sets of values that are duplicated, follow the steps given below.

  • Determine which columns contain the values that may be duplicated.

  • List those columns in the column selection list, along with the COUNT(*).

  • List the columns in the GROUP BY clause as well.

  • Add a HAVING clause that eliminates the unique values by requiring the group counts to be greater than one.

Eliminating Duplicates from a Query Result

You can use the DISTINCT command along with the SELECT statement to find out unique records available in a table.

mysql> SELECT DISTINCT last_name, first_name -> FROM person_tbl -> ORDER BY last_name;

An alternative to the DISTINCT command is to add a GROUP BY clause that names the columns you are selecting. This has the effect of removing duplicates and selecting only the unique combinations of values in the specified columns.

mysql> SELECT last_name, first_name -> FROM person_tbl -> GROUP BY (last_name, first_name);

Removing Duplicates Using Table Replacement

If you have duplicate records in a table and you want to remove all the duplicate records from that table, then follow the procedure given below.

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex -> FROM person_tbl; -> GROUP BY (last_name, first_name); mysql> DROP TABLE person_tbl; mysql> ALTER TABLE tmp RENAME TO person_tbl;

An easy way of removing duplicate records from a table is to add an INDEX or a PRIMARY KEY to that table. Even if this table is already available, you can use this technique to remove the duplicate records and you will be safe in future as well.

mysql> ALTER IGNORE TABLE person_tbl -> ADD PRIMARY KEY (last_name, first_name);
Sours: https://www.tutorialspoint.com/mysql/mysql-handling-duplicates.htm
MySql 34 - Find Duplicate Records in MySQL

Summary: in this tutorial, you will learn how to find duplicate values of one or more columns in MySQL.

Data duplication happens because of many reasons. Finding duplicate values is one of the important tasks that you must deal with when working with the databases.

Setting up a sample table

First, create a table named with four columns: , , , and .

Code language:SQL (Structured Query Language)(sql)

Second, inserts rows into the table:

Code language:SQL (Structured Query Language)(sql)

Third, query data from the contacts table:

Code language:SQL (Structured Query Language)(sql)

In the table, we have some rows that have duplicate values in the , , and columns. Let’s learn how to find them.

Find duplicate values in one column

The find duplicate values in on one column of a table, you use follow these steps:

  1. First, use the clause to group all rows by the target column, which is the column that you want to check duplicate.
  2. Then, use the function in the clause to check if any group have more than 1 element. These groups are duplicate.

The following query illustrates the idea:

Code language:SQL (Structured Query Language)(sql)

By using this query template, you can to find rows that have duplicate emails in the table as follows:

Code language:SQL (Structured Query Language)(sql)

This picture shows the output of the query that shows the duplicate emails:

MySQL find duplicate values example

Find duplicate values in multiple columns

Sometimes, you want to find duplicate rows based on multiple columns instead of one. In this case, you can use the following query:

Code language:SQL (Structured Query Language)(sql)

Rows are considered duplicate only when the combination of columns are duplicate therefore we used the operator in the clause.

For example, to find rows in the table with duplicate values in , , and column, you use the following query:

Code language:SQL (Structured Query Language)(sql)

The following illustrates the output of the query:

MySQL find duplicate values on multiple columns

In this tutorial, you have learned how to find duplicate rows based on value of one or more columns in MySQL.

Sours: https://www.mysqltutorial.org/mysql-find-duplicate-values/

You will also like:

MySQL INSERT ON DUPLICATE KEY UPDATE

next →← prev

The Insert on Duplicate Key Update statement is the extension of the INSERT statement in MySQL. When we specify the ON DUPLICATE KEY UPDATE clause in a SQL statement and a row would cause duplicate error value in a UNIQUE or PRIMARY KEY index column, then updation of the existing row occurs.

In other words, when we insert new values into the table, and it causes duplicate row in a UNIQUE OR PRIMARY KEY column, we will get an error message. However, if we use ON DUPLICATE KEY UPDATE clause in a SQL statement, it will update the old row with the new row values, whether it has a unique or primary key column.

For example, if column col1 is defined as UNIQUE and contains the value 10 into the table tab1, we will get a similar effect after executing the below two statements:

It makes sure that if the inserted row matched with more than one unique index into the table, then the ON DUPLICATE KEY statement only updates the first matched unique index. Therefore, it is not recommended to use this statement on tables that contain more than one unique index.

If the table contains AUTO_INCREMENT primary key column and the ON DUPLICATE KEY statement tries to insert or update a row, the Last_Insert_ID() function returns its AUTO_INCREMENT value.

The following are the syntax of Insert on Duplicate Key Update statement in MySQL:

In this syntax, we can see that the INSERT statement only adds the ON DUPLICATE KEY UPDATE clause with a column-value pair assignment whenever it finds duplicate rows. The working of ON DUPLICATE KEY UPDATE clause first tries to insert the new values into the row, and if an error occurs, it will update the existing row with the new row values.

The VALUES() function only used in this clause, and it does not have any meaning in any other context. It returns the column values from the INSERT portion and particularly useful for multi-rows inserts.

MySQL gives the number of affected-rows with ON DUPLICATE KEY UPDATE statement based on the given action:

  • If we insert the new row into a table, it returns one affected-rows.
  • If we update the existing row into a table, it returns two affected-rows.
  • If we update the existing row using its current values into the table, it returns the number of affected-rows 0.

MySQL INSERT ON DUPLICATE KEY Example

Let us understand the working of the INSERT ON DUPLICATE KEY UPDATE clause in MySQL with the help of an example.

First, create a table named "Student" using the below statement:

Next, insert the data into the table. Execute the following statement:

Execute the SELECT statement to verify the insert operation:

We will get the output as below where we have three rows into the table:

MySQL INSERT ON DUPLICATE KEY UPDATE

Again, add one more row into the table using the below query:

The above statement will add row successfully because it does not have any duplicate values.

MySQL INSERT ON DUPLICATE KEY UPDATE

Finally, we are going to add a row with a duplicate value in the Stud_ID column:

MySQL gives the following message after successful execution of the above query:

In the below out, we can see that the row id=4 already exists. So the query only updates the City New York with California.

MySQL INSERT ON DUPLICATE KEY UPDATE

Next TopicMySQL INSERT IGNORE



← prevnext →



Sours: https://www.javatpoint.com/mysql-insert-on-duplicate-key-update


588 589 590 591 592