0

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 enter image description here

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.

enter image description here

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

  1. ON DUPLICATE KEY UPDATE only allow to inserted a row that would cause a duplicate value in a UNIQUE index or PRIMARY KEY
Community
  • 1
  • 1
Wee Hong
  • 585
  • 2
  • 8
  • 23
  • Can you post your attempts? What is your criterion for updating the `amount` column? – Sameer Mirji Feb 04 '16 at 04:14
  • @SameerMirji, I updated my format. – Wee Hong Feb 04 '16 at 05:04
  • Have a look at this [post](http://stackoverflow.com/questions/2714587/mysql-on-duplicate-key-update-for-multiple-rows-insert-in-single-query) – 1000111 Feb 04 '16 at 05:28
  • @SubrataDeyPappu, I read it already. It's not working. – Wee Hong Feb 04 '16 at 05:43
  • 1
    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 Feb 04 '16 at 06:05
  • @JimishGamit, so the only way is to use PHP and use LOOP to update? :( – Wee Hong Feb 04 '16 at 07:28
  • Can you please elaborate more clearly which records you want to update and when you want to insert ? examples will be good to understand your problem – Jimish Gamit Feb 04 '16 at 07:31

1 Answers1

0

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 ;
Sameer Mirji
  • 2,135
  • 16
  • 28