2

To summarize, I have an update query that will tack on a string of data (e.g. SAVE15) to an existing field. Currently, I anticipate this field to already have some information in it so my values are appending as ", SAVE15" which is a comma and space separator. This works for now but soon I anticipate the need to insert a comma and space only if the field is NOT NULL. If it is null, I need it to insert "SAVE15". Here is my current query:

UPDATE sales_flat_order sfo
INNER JOIN sales_flat_order_grid sfog
ON sfog.entity_id = sfo.entity_id
SET sfo.coupon_code = concat(IFNULL(sfo.coupon_code, ""),", SAVE15")
WHERE sfog.increment_id = "12345678";

Here is my attempt to use CONCAT_WS to add a separator ony when necessary:

UPDATE sales_flat_order sfo
INNER JOIN sales_flat_order_grid sfog
ON sfog.entity_id = sfo.entity_id
SET sfo.coupon_code = CONCAT_WS(',',IFNULL(sfo.coupon_code, ""),"SAVE15")
WHERE sfog.increment_id = "12345678";

I thought it was working at first but then it inserted ",SAVE15" onto a null field. I believe the second query is the correct method but I seem to be using it incorrectly.

sparecycle
  • 2,038
  • 5
  • 31
  • 58

1 Answers1

4

The documentation for Concat_WS tell us that it will skip any Null field but not an empty string. You must therefore remove the call to IfNull(sfo.coupon_code, "") that convert the Null value into an empty string.

SylvainL
  • 3,926
  • 3
  • 20
  • 24