mysql if condition is met

Question

I am using MySQL (phpmyadmin) for my database. I want to change the value in a field depending on another's field info.

My table would be "info" and my column1 store_number, column2 store_name and column3 state, so if in field store_name there is a character string like Store number 3, then change column1 value to 3.

I tried

UPDATE info
IF store_name LIKE "%store number 3 %"
SET store_number = '3'
WHERE state LIKE "%california%"

I want to change the store number in all stores in california depending in the store name.

score 0 · Accepted Answer · answered Jun 01 '18 at 16:53

0

You were on the right track with your WHERE clause already. You could move your conditional logic to be part of it to get:

UPDATE info
SET store_number='3'
WHERE state LIKE '%california%' AND 
  store_name LIKE '%store number 3%'

answered Jun 01 '18 at 16:53

Allen G

438
4
6

Thanks my man, i was wondering if i want to change the store number using diferent conditions, can i use like this: UPDATE info SET store_number ='3' (CASE WHEN (store_name LIKE "%store number 3 %") THEN '3' WHEN (store_name LIKE "%store number 4%") THEN '4' END) WHERE state LIKE "%california%" – Therealguma Jun 01 '18 at 17:15
@Therealguma You can definitely incorporate `CASE` in an update statement. You can see [this post](https://stackoverflow.com/questions/12754470/mysql-update-case-when-then-else) for an example :) – Allen G Jun 01 '18 at 18:39
Dude, one last thing... let's say i want it to show store name and store number from more than one state... let's say: `code` SELECT store_number, store_name FROM info WHERE state ='california' (but here i also need another state, multiple states in the condition WHERE... ) tried WHERE state='california','nevada' did not work. – Therealguma Jun 04 '18 at 15:26
You can use `OR`: `... WHERE state='california' OR state='nevada'`. – Allen G Jun 05 '18 at 16:23
Thanks man, i use LIKE to get more results (california1, california2, etc) It does not work when i use 'code' WHERE state LIKE "%california%" OR "%nevada%", it only displays "%california%" results... – Therealguma Jun 05 '18 at 17:21
I just firured it out man, i used 'code' WHERE state LIKE "%california%" OR state LIKE "%nevada%" – Therealguma Jun 05 '18 at 17:25
Thank you a lot for all your help!! – Therealguma Jun 05 '18 at 17:26

mysql if condition is met

1 Answers1