MySQL ON DUPLICATE KEY UPDATE

INSERT ... ON DUPLICATE KEY UPDATE inserts a new row when no unique-key conflict occurs and updates the existing row when an PRIMARY KEY or other UNIQUE key conflicts.

Duplicate-key conflict without an upsert clause

INSERT INTO student3 (id, name, class, social, science, math)
VALUES (2, 'Max Ruin', 'Three', 86, 57, 86);

Update the existing row instead

INSERT INTO student3 (id, name, class, social, science, math)
VALUES (2, 'Max Ruin', 'Three', 86, 57, 86) AS new
ON DUPLICATE KEY UPDATE
    social = new.social,
    science = new.science,
    math = new.math;

The row alias new represents the values that would have been inserted. Current MySQL recommends this form instead of the older VALUES(column) form.

Update multiple existing rows

INSERT INTO student3 (id, name, class, social, science, math)
VALUES
    (2, 'Max Ruin', 'Three', 86, 57, 86),
    (3, 'Arnold', 'Three', 56, 41, 76),
    (4, 'Krish Star', 'Four', 62, 52, 72),
    (5, 'John Mike', 'Four', 62, 82, 92),
    (6, 'Alex John', 'Four', 58, 93, 83),
    (7, 'My John Rob', 'Fifth', 79, 64, 74),
    (8, 'Asruid', 'Five', 89, 84, 94),
    (9, 'Tes Qry', 'Six', 77, 61, 71),
    (10, 'Big John', 'Four', 56, 44, 56) AS new
ON DUPLICATE KEY UPDATE
    social = new.social,
    science = new.science,
    math = new.math;

Update existing rows and insert a new row

INSERT INTO student3 (id, name, class, social, science, math)
VALUES
    (2, 'Max Ruin', 'Three', 86, 57, 86),
    (3, 'Arnold', 'Three', 56, 41, 76),
    (4, 'Krish Star', 'Four', 62, 52, 72),
    (5, 'John Mike', 'Four', 62, 82, 92),
    (6, 'Alex John', 'Four', 58, 93, 83),
    (7, 'My John Rob', 'Fifth', 79, 64, 74),
    (8, 'Asruid', 'Five', 89, 84, 94),
    (9, 'Tes Qry', 'Six', 77, 61, 71),
    (10, 'Big John', 'Four', 56, 44, 56),
    (11, 'New Name', 'Five', 75, 78, 52) AS new
ON DUPLICATE KEY UPDATE
    social = new.social,
    science = new.science,
    math = new.math;

Rows whose key already exists are updated; ID 11 is inserted if it is new.

Affected-row count

• 1 affected row for a newly inserted row.
• 2 affected rows for a row updated to different values.
• 0 affected rows when an existing row is assigned the same values, unless the client uses found-rows behavior.

Download the student3 SQL dump

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: