How conditionally update a column that contains delimited values with ON DUPLICATE KEY UPDATE syntax

Question

I am trying to update multiple records (about a thousand of them) using this single statement (this is a process that will run every night). The statement below only includes 3 products for simplicity:

INSERT INTO productinventory
  (ProductID, VendorID, CustomerPrice, ProductOverrides)
VALUES
  (123, 3, 100.00, 'CustomerPrice'),
  (124, 3, 100.00, 'CustomerPrice'),
  (125, 3, 100.00, 'CustomerPrice')
ON DUPLICATE KEY UPDATE
  CustomerPrice = VALUES(CustomerPrice),
  ProductOverrides = CONCAT_WS(',', ProductOverrides, 'CustomerPrice')
;

Everything works fine except that the ProductOverrides column gets the text 'CustomerPrice' added to it every time this statement runs, so it ends up looking like this after it runs twice:

CustomerPrice,CustomerPrice

What I want the statement to do is to add 'CustomerPrice' to the ProductOverrides column, but only if that string does not already exist there. So that no matter how many times I run this statement, it only includes that string once. How do I modify this statement to achieve that?

See [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/a/3653574) — eggyal, Sep 28 '13 at 15:40
I don't think this will solve my problem without creating a whole lot more problems. The ProductOverrides column tells the system not update a column when the main data feed runs. It looks for a column name in that field and ignores it if it exists. There has got to be a way to check if a string exists in a column, then ignore it in MYSQL. — KYLE, Sep 28 '13 at 15:46

peterm · Accepted Answer · 2013-09-28T15:52:24.933

0

You can do something like this

INSERT INTO productinventory (ProductID, VendorID, CustomerPrice, ProductOverrides) 
VALUES 
(123, 3, 100.00, 'CustomerPrice'), 
(124, 3, 100.00, 'CustomerPrice'), 
(125, 3, 100.00, 'CustomerPrice') 
ON DUPLICATE KEY UPDATE 
  CustomerPrice = VALUES(CustomerPrice), 
  ProductOverrides = IF(FIND_IN_SET(VALUES(ProductOverrides), ProductOverrides) > 0, 
                        ProductOverrides, 
                        CONCAT_WS(',', ProductOverrides, VALUES(ProductOverrides)));

Here is SQLFiddle demo

edited Sep 28 '13 at 15:52

answered Sep 28 '13 at 15:45

peterm

91,357
15
148
157

The ProductOverrides column will contain a list of other column names that the system is to ignore during other update processes. So a typical value in the ProductOverrides column may look like this before the above SQL statement runs: "Title,Description". Then after it runs, I want it to look like: "Title,Description,CustomerPrice". Would that be possible using the IF() you have above? – KYLE Sep 28 '13 at 15:50
I understand that and my example does exactly that. I've updated sql fiddle example. Take a closer look at it. If you already have in a list a `ProductOverrides` value that you pass in your insert it won't add it again. – peterm Sep 28 '13 at 15:54
Yep, you're right. I ran it and it worked perfectly. Thank you! – KYLE Sep 28 '13 at 16:05

How conditionally update a column that contains delimited values with ON DUPLICATE KEY UPDATE syntax

1 Answers1