How to update with concat and if statement?

Question

I have a table with contact codes. When contact code is not specified there are ^^ in the empty cell. If specified should be: ^Code123^,^Code321^,^Code987^

I want to update this cell, but I dont know how to rewrite empty cell with ^^?

With my query I get this result ^^,^Code123^,^Code321^,^Code987^. How to delete ^^ when updating cell?

$sql6 = "UPDATE contacts 
         SET clicks_c=IF(clicks_c='', 
                         '^".$url_name."^',
                         CONCAT_WS(',',clicks_c,'^".$url_name."^')) 
         WHERE id_c='".$row_id."'";

result with my query is ^^,^Code123^,^Code321^,^Code987^ but I want ^Code123^,^Code321^,^Code987^ — user3514052, Apr 10 '19 at 08:38
This is a horrible data format. You should normalize your tables, not put a list in a column. — Barmar, Apr 10 '19 at 08:50
I don't see where `^^` is coming from in your query. Can `$url_name` be empty? — Barmar, Apr 10 '19 at 08:53

score 1 · Accepted Answer · answered Apr 10 '19 at 08:56

1

If an empty cell contains ^^, you need to test for that in your IF(). Change:

IF(clicks_c='',

to:

IF(clicks_c IN ('', '^^),

Then it will replace ^^ with a nonempty code, instead of concatenating to it.

answered Apr 10 '19 at 08:56

Barmar

741,623
53
500
612

How to update with concat and if statement?

1 Answers1