Changing multiple values in a MySQL table

Question

I need to change the value of a specific field/column of multiple records and between specific dates, in a MySQL table. Using the MySQL workbench v5.2. So for example I need to check if the value is over x, between two specific dates. If it is over x, then I need to make it = y. I was trying the below statement which doesn't work. It's giving me an error on the WHERE clause.

UPDATE `mydb`.`mytable` WHERE `Time_Stamp`
BETWEEN '2014-12-31 00:00:00'
    AND '2014-11-31 06:00:00'
IF `my_Column` > x Then
    `my_Column` = y
End IF;

Even if it does accept the WHERE clause, I'm not sure the IF statement will work following on from that. As you can see I'm a learner. So any help would be much appreciated.

Saharsh Shah · Accepted Answer · 2014-12-31 14:11:01Z

1

Try this:

UPDATE mydb.mytable 
SET my_Column = CASE WHEN my_Column > x THEN y ELSE x END
WHERE Time_Stamp BETWEEN '2014-12-31 00:00:00' AND '2014-11-31 06:00:00'

answered Dec 31, 2014 at 14:11

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

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

Collectives™ on Stack Overflow

Changing multiple values in a MySQL table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related