I have two tables X and Y. Both of them have the column ‘name’ in common.
Columns of X are
- id(Auto increment) – primary key
- name
- value.
Columns of Y are
- id(Auto increment) – primary key
- name
- sex
- 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';