Skip to content
Advertisement

MYSQL insert into on duplicate key value

I have two tables X and Y. Both of them have the column ‘name’ in common.

Columns of X are

  1. id(Auto increment) – primary key
  2. name
  3. email
  4. value.

Columns of Y are

  1. id(Auto increment) – primary key
  2. name
  3. sex
  4. salary

There are some rows in X which are not present in Y. I want to insert those missing rows into Y. Along with that I also want to update the existing records in Y which have the same name as records in X.

I am trying to run the following query.

INSERT INTO Y (name, sex, salary) 
SELECT X.name, 'FEMALE' AS sex, '1000' AS salary 
FROM X LEFT JOIN Y ON X.name=Y.name 
WHERE X.email LIKE '%@test.com' 
ON DUPLICATE KEY UPDATE sex='MALE';

But when I run this query, instead of updating the existing records with same name in table Y, it inserts new ones.

So I wanted to know does “On DUPLICATE KEY UPDATE” only compare using the primary keys. Is it possible to give any different column(name) in that clause.

Advertisement

Answer

Change your query like below. The comparison happens on primary key column or on unique constraint on a (group of) column(s). If a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. See Documentation for more information.

INSERT INTO Y (name, sex, salary) 
SELECT X.name, 'FEMALE' AS sex, '1000' AS salary 
FROM X LEFT JOIN Y ON X.name=Y.name 
WHERE X.email LIKE '%@test.com' 
AND Y.name IS NULL
ON DUPLICATE KEY UPDATE Y.sex='MALE';

(OR) separate them in two queries like INSERT

INSERT INTO Y (name, sex, salary) 
SELECT X.name, 'FEMALE' AS sex, '1000' AS salary 
FROM X LEFT JOIN Y ON X.name = Y.name 
WHERE X.email LIKE '%@test.com' 
AND Y.name IS NULL;

Perform UPDATE

UPDATE Y 
JOIN X ON X.name = Y.name
 SET Y.sex='MALE'
WHERE X.email LIKE '%@test.com';
Advertisement