MySQL: how to overrride a row with same Primary key

Question

Let's say I created the following table:

CREATE TABLE `my_table` (
  num1 INT PRIMARY KEY,
  num2 INT,
  num3 INT
) NGINE=InnoDB DEFAULT CHARSET=utf8;

Let's also say that I want to insert a new row to table, and if the new row has the same primary key as one of the existing rows in the table, I want the new row to override the correspondent row.

Currently, the following SQL query will do the job:

INSERT INTO my_table (num1, num2, num3) VALUES (1, 2, 3)
ON DUPLICATE KEY UPDATE num1 = num1, num2 = num2, num3 = num3;

The problem with this query is that it's cumbersome. Imagine a table with 20 columns. I'll have to write again all column names.

Do you know any elegant way to make this happen?

*"The problem with this query is that it's cumbersome. Imagine a table with 20 columns. I'll have to write again all column names."* Indeed that's why you should normalize as incrementing column names like you have are great candidates for normalisation... If this is "pseudo" code you should give a better user case which matches your question better and or user case better — Raymond Nijland, Apr 30 '19 at 17:20

score 1 · Answer 1 · answered Apr 30 '19 at 17:24

Well, you can skip the primary key, because that has the same value as the existing row by definition, or else it wouldn't be a duplicate row.

Note that you use VALUES(columname) or else you won't be overriding the values with the ones from your VALUES list.

INSERT INTO my_table (num1, num2, num3) VALUES (1, 2, 3)
ON DUPLICATE KEY UPDATE num2 = VALUES(num2), num3 = VALUES(num3);

Or as the comment above mentioned, use REPLACE:

REPLACE INTO my_table (num1, num2, num3) VALUES (1, 2, 3);

(Note that these do two different things. You might like to read my answer to "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE".)

The problem with this query is that it's cumbersome. Imagine a table with 20 columns. I'll have to write again all column names.

Hmm. Are the Copy & Paste buttons on your computer broken?

Perhaps you would like us to trim the crust off your bread for you? (joking)

Honestly, sometimes software development involves a bit of work. Typing 20 column names is not the most boring thing you're going to do this week.

*"Typing 20 column names is not the most boring thing you're going to do this week. "* About time they implement a IDE with voice to text so you can talk nerd (programming) language.. I wonder how this would work on a busy office... — Raymond Nijland, Apr 30 '19 at 17:28
@RaymondNijland that would be fun for speech-to-code-bombing pranks... imaging walking around devops guys reciting "sudo ar em space hyphen ar ef space slash" and then run away like crazy before they butcher you :) — Javier Larroulet, Apr 30 '19 at 17:49

score 1 · Accepted Answer · answered Apr 30 '19 at 17:25

Say hi to MySQL's REPLACE.

Per mySQL documentation:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted

Thus, you could simply

REPLACE INTO my_table (num1, num2, num3) VALUES (1,2,3);

if the row does not exist, it'll work as a regular INSERT. If it does exist, it'll be deleted+inserted (in your eyes, it'll look like an UPDATE)

Be advised. You could feel tempted to always use REPLACE instead of INSERT regardless of what you're doing. That would inconvenient (I learned the hard way) as the Replace has a lot more overhead

MySQL: how to overrride a row with same Primary key

2 Answers2