MySQL : IF EXISTS THEN UPDATE ELSE INSERT from one table to another using IF ELSE statement

Question

I have two tables user & user_tmp having primary key as id only. The fields of both the tables are likely as :

`id` PRIMARY AUTO_INCREMENT INT,
`Name` VARCHAR(80),
`Contact No` VARCHAR(10),
`Status` INT

I need a simple query based on IF EXISTS ON user_tmp table THEN update value in user ELSE insert in user table.

I tried with REPLACE & ON DUPLICATE KEY, but it din't work as id is the PRIMARY KEY and the value varies in both the tables.

Regards.

if `id` value doesn't match in two tables how can you distinguish unique records? — Alexey, May 11 '17 at 06:52
Hi Alexey, this is based on `Contact_No`, which is also not a contraint — Pallab DB, May 11 '17 at 06:54
How about adding it as a unique key to user table and using `on duplicate key update` ? — Alexey, May 11 '17 at 06:57
You can't use `REPLACE` or `ON DUPLICATE KEY` unless the column you're testing is a unique column. If you can't make `make `Contact_No` unique, you can't do this in pure SQL. You'll need to write a stored procedure to do it. — Barmar, May 11 '17 at 07:13

score 1 · Accepted Answer · edited May 23 '17 at 11:47

You need to do it as two queries. One query to update the records that match:

UPDATE user AS u
JOIN user_tmp AS t ON u.Contact_No = t.Contact_no
SET u.Name = t.Name, u.Status = t.Status;

and a second to insert the ones that don't already exist:

INSERT INTO user (Name, Contact_No, Status)
SELECT t.Name, t.Contact_No, t.Status
FROM user_tmp AS t
LEFT JOIN user AS u ON u.Contact_No = t.Contact_no
WHERE t.id IS NULL

See Return row only if value doesn't exist for an explanation of how the SELECT query in the INSERT works to find rows that don't match.

MySQL : IF EXISTS THEN UPDATE ELSE INSERT from one table to another using IF ELSE statement

1 Answers1