mysql update different columns based on different conditions

Question

I have this simple table:

list1 (VARCHAR), list2 (VARCHAR), list3 (VARCHAR), list1cnt (INT), list2cnt (INT), list3cnt (INT)

Now I want to use one single mysql query to see which of the lists have the word 'book' inside, and update the proper 'cnt' columns accordingly.

So if list1 has the word 'book' inside, increment list1cnt value;

if list2 has the word 'book' inside, increment list2cnt value;

if list3 has the word 'book' inside, increment list3cnt value;

So far my query is this (as you can see, I am running 3 separate queries).

mysqli_query($link,"UPDATE table SET list1cnt+='1' WHERE list1 LIKE '%book%'");
mysqli_query($link,"UPDATE table SET list2cnt+='1' WHERE list2 LIKE '%book%'");
mysqli_query($link,"UPDATE table SET list3cnt+='1' WHERE list3 LIKE '%book%'");

Right now if I use this on multiple sets of data, I am sending 3 queries. Each query takes more time if passed using mysqli_query in PHP, so I am looking for a 3in1 MYSQL solution with IF's and conditions in MYSQL directly. And I found something but problem is that this doesn't work, so I am stuck:

UPDATE table SET `list1cnt`=(CASE WHEN `list1` LIKE '%book%' THEN (`list1cnt`+'1')) WHERE date<='$date'

Michael · Accepted Answer · 2014-01-28 15:52:07Z

2

You can just use IF:

UPDATE
  `table`
SET
  `list1cnt` = IF (`list1` LIKE '%book%', `list1cnt` + 1, `list1cnt`),
  `list2cnt` = IF (`list2` LIKE '%book%', `list2cnt` + 1, `list2cnt`),
  `list3cnt` = IF (`list3` LIKE '%book%', `list3cnt` + 1, `list3cnt`),

answered Jan 28, 2014 at 15:52

Michael

12.9k6 gold badges57 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2014-01-28 15:48:41Z

1

You can change multiple columns in an update. In this case, you want conditional updates:

UPDATE table
    SET list1cnt = (case when list1 like '%book% then  list1cnt + 1 else list1cnt end),
        list2cnt = (case when list2 like '%book% then  list2cnt + 1 else list2cnt end),
        list3cnt = (case when list3 like '%book% then  list3cnt + 1 else list3cnt end)
        list1cnt += 1
    WHERE list1 LIKE '%book%' or list2 LIKE '%book%' or list3 LIKE '%book%';

answered Jan 28, 2014 at 15:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

mysql update different columns based on different conditions

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related