ON DUPLICATE KEY UPDATE with WHERE

Question

I've got an INSERT ... ON DUPLICATE KEY UPDATE query and I am trying to add WHERE clause to it:

INSERT INTO `product_description` (
  `product_id`,`language_id`,`name`,
  `description`,`meta_description`,
  `meta_keyword`,`tag`
) VALUES (
  $getProductId, $languageId, '$pName', '$pDescription', '', '', ''
)
ON DUPLICATE KEY UPDATE
  `name` = '$pName',
  `description` = '$pDescription'

I want to restrict the UPDATE to those 2 conditions:

WHERE  `model` = 'specific-model' AND `sku` NOT LIKE '%B15%'

If I add this part of query to the end of the original query I get a MySQL syntax error. What would be a working solution?

Update: Please note that model and sku are in another table, and the common key is product_id

possible duplicate of [INSERT ... ON DUPLICATE KEY UPDATE with WHERE?](http://stackoverflow.com/questions/2469457/insert-on-duplicate-key-update-with-where) — Elon Than, Sep 14 '15 at 21:57
You need to post your whole query so we can see where your syntax error is. However, your `WHERE` statement should go before the `ON DUPLICATE` statement. — BrokenBinary, Sep 14 '15 at 21:58
@BrokenBinary I don't think you can add `WHERE` in `INSERT` queries. — Elon Than, Sep 14 '15 at 22:07
A `WHERE` clause makes absolutely 0 sense here. If a key is unique and the `INSERT` specifies an existing value, then the record is **known** so what would be the point of a `WHERE`? You're obviously trying to update another table - so create an `AFTER UPDATE` trigger on that table and update the 2nd table. — N.B., Sep 15 '15 at 20:59
Have you thought about creating a stored procedure? In the stored procedure, you can check whether model and sku are as you desire. Only then execute the `INSERT...ON DUPLICATE...`. Otherwise, throw an exception or do something else. — zedfoxus, Sep 16 '15 at 22:15

score 4 · Accepted Answer · edited Sep 23 '15 at 12:17

I would suggest you to use some sort of prepared statement instead of concatenating strings, so you should do something like this:

INSERT INTO `product_description` (
  `product_id`, `language_id`, `name`,
  `description`, `meta_description`,
  `meta_keyword`, `tag`
) VALUES (?, ?, ?, ?,'','','')

but this is not part of the question.

I was thinking of answering with a simple CASE WHEN but the challenging part of your question is that the restrict conditions are not in the product_description table but are from another table. So I think we can just use a TRIGGER:

CREATE TRIGGER product_description_upd
BEFORE UPDATE ON product_description
FOR EACH ROW
    IF NOT EXISTS(SELECT * FROM models
              WHERE product_id=new.product_id
              AND model='Abc' AND `sku` NOT LIKE '%B15%') THEN
      SET new.name=old.name;
      SET new.description=old.description;
    END IF;
//

then you can use an INSERT query like:

INSERT INTO `product_description` (col1, col2, ...)
VALUES (..., ..., ...)
ON DUPLICATE KEY
UPDATE name=VALUE(name),description=VALUE(description)

Please see a fiddle here.

The only thing to note here is that even a standard UPDATE query will be affected.

Hemant Kumar · Answer 2 · 2015-09-23T20:24:37.280

CREATE TABLE product_description (
  product_id INT PRIMARY KEY,
  name VARCHAR(100),
  description VARCHAR(100)
);

CREATE TABLE models (
  product_id INT,
  model VARCHAR(100),
  sku VARCHAR(100)
);

INSERT INTO models VALUES
(1, "Abc", "ZZZ"),
(2, "Abc", "B15");

INSERT INTO product_description VALUES
(1, "Car", "Red"),
(2, "Truck", "Pink");

INSERT INTO `product_description` VALUES (1, "NewCar", "DeepRed")
ON DUPLICATE KEY UPDATE name=VALUES(name), description=VALUES(description);

Assuming, product_id must be in models.

INSERT INTO `product_description` (product_id, name, description)
  SELECT models.product_id, "SuperCar" as name, "DarkRed" as description 
  FROM `models` WHERE model="Abc" AND `sku` NOT LIKE "%B15%"
ON DUPLICATE KEY UPDATE name="UpdatedCar", description="UpdatedRed";

refer to http://sqlfiddle.com/#!9/69624e/1

Hopefully this solves the problem. You can play with SELECT query for different result.

ON DUPLICATE KEY UPDATE with WHERE

2 Answers2