MySQL - Insert Multiple Records or Update Multiple Records If Exists

Question

I want to INSERT a new record into my database if it is no exists, otherwise it will UPDATE those existing records.

I did search on Stackoverflow, but none of the result can solve my issue.

TABLE STRUCTURE

SCENARIO

There are a lot of records inside Purchase Details table. So the only Unique ID is only purchase_id.

I want to update the amount which have the same product_id. Refer to the table.

Below is the SQL Query I have tried so far.

INSERT INTO `purchase_details` (`product_id`, `amount`)
VALUES (1583, 0)
ON DUPLICATE KEY UPDATE amount = 0

The query shows 1 row affected.

But total has 146 rows which mean this query is not working.

PROBLEM

ON DUPLICATE KEY UPDATE only allow to inserted a row that would cause a duplicate value in a UNIQUE index or PRIMARY KEY

Can you post your attempts? What is your criterion for updating the amount column? — Sameer Mirji
If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row. purchase_id is your PK so I believe it's auto incremental..so your insert will never going to produce duplicate row..ON DUPLICATE KEY UPDATE will not help here :( — Jimish Gamit

Sameer Mirji Sameer Mirji · Accepted Answer · 2016-02-04T16:27:06

If I understand your question correctly, you want to update the other existing rows of your table which have the same product_id as the new one being inserted with the newly provided amount value.

Since product_id is not a unique column in purchase_details table, it will not consider this scenario for ON DUPLICATE KEY UPDATE condition. Ergo, 1 row affected.

To achieve what you want, you need to CREATE TRIGGER.

Like this:

DELIMITER $$
CREATE TRIGGER ins_check 
AFTER INSERT ON purchase_details
FOR EACH ROW
BEGIN
    UPDATE purchase_details
       SET amount = NEW.amount
     WHERE product_id = NEW.product_id;
END;$$
DELIMITER ;

MySQL - Insert Multiple Records or Update Multiple Records If Exists

1 Answers