On Duplicate Key Update Generating Duplicate Key Exception

Posted By admin On 12.12.20

Duplicate key update にはcaseやifが使えるので、フィールド毎に更新条件を設定できる。 デフォルト値を使って値を更新されないようにもできる。 取得した数字がレコードの数字より大きければ更新. ON DUPLICATE KEY UPDATE inserts or updates a row, the LASTINSERTID function returns the AUTOINCREMENT value. Exception: For updates, LASTINSERTID is not meaningful prior to MySQL 5.1.12. However, you can work around this by using LASTINSERTID(expr). Jun 18, 2012  Join a community of over 2.6m developers to have your questions answered on Duplicate key exception on report generate of Reporting Reporting. Start with our free trials.

From MySQL 4.1.0 onwards, it is possible to add ON DUPLICATE KEY UPDATE statement to specify behavior when values inserted (with INSERT or SET or VALUES) are already in destination table w.r.t. PRIMARY KEY or some UNIQUE field. If value for PRIMARY KEY or some UNIQUE field are already in table, INSERT is replaced by an UPDATE.

  • How does ON DUPLICATE KEY UPDATE behave in case there are multiple
    UNIQUE fields in my table ?

  • Can I have one update only, if either UNIQUE field is matched ?

  • Can I have an update only if both UNIQUE fields are matched simultaneously ?

Answers:

Consider

if a and b are UNIQUE fields, UPDATE occurs on a = 1 OR b = 2. Also when condition a = 1 OR b = 2 is met by two or more entries, update is done only once.

Ex here table table with Id and Name UNIQUE fields

If query is

then we get

which violates uniqueness of Id and Name. Now with

we get

Behavior on multiple keys is the following

On Duplicate Key Update Generating Duplicate Key Exception For Business

UPDATE in ON DUPLICATE KEY UPDATE is performed if one of the UNIQUE field equals the value to be inserted. Here, UPDATE is performed on Id = 1 OR Name = C. It is equivalent to

What if I want one update only, for either key

Can use UPDATE statement with LIMIT keyword

which will give

On Duplicate Key Update Generating Duplicate Key Exception Free

Key

What if I want one update only if values for both keys are matched

One solution is to ALTER TABLE and make the PRIMARY KEY (or uniqueness) work on both fields.

Now, on

we get

since no duplicate (on both keys) is found.

Answers:

  1. how does MySQL behave …
    It behaves as expected, that is executes ON DUPLICATE KEY clause.

  2. Can I have one update for either…
    In reality, you have only one ON DUPLICATE KEY clause, so you need to put some code to differentiate which constraint was involved. Fortunatelly, it is possible. The only thing you should know, the order of assignment matters, and you can assign multiple times. Suppose, you have unique constraint on a and b, and you want to update c only if a uniqueness is involved:
    …
    KEY UPDATE c = IF(a = VALUES(a) and b <> VALUES(b), VALUES(c), c), b = VALUES(b)

    but if you change the order of assignments the second condition within if will be always false.

  3. See 2.

Tags: date, mysql, sql

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.

Introduction to the MySQL INSERT ON DUPLICATE KEY UPDATE statement

The INSERT ON DUPLICATE KEY UPDATE is a MySQL’s extension to the SQL standard’s INSERT statement.

On Duplicate Key Update Generating Duplicate Key Exception

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.

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.

The syntax of INSERT ON DUPLICATE KEY UPDATE statement is as follows:

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.

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 ON DUPLICATE KEY UPDATE clause.

Generate new ssh host keys centos. 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 INSERT clause in the DUPLICATE KEY UPDATE clause, you use the VALUES() function as follows:

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.

MySQL INSERT ON DUPLICATE KEY UPDATE example

Let’s take a look at an example of using the INSERT ON DUPLICATE KEY UPDATE to understand how it works.

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

On Duplicate Key Update Generating Duplicate Key Exception Windows 7

Next, insert rows into the devices table.

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

Now, we have three rows in the devices table.

After that, insert one more row into the devices table.

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

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

On Duplicate Key Update Generating Duplicate Key Exception Form

MySQL issues the following message:

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

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.