Menu
How do I update a table and set different values upon the condition evaluating to True.
For instance :
UPDATE Table SET A = '1' IF A > 0 AND A < 1 SET A = '2' IF A > 1 AND A < 2 WHERE A IS NOT NULL; I have seen CASE expression and IF expression in Procedures and Functions but I want to use it in a simple update/select statement. Is it possible or am I expecting too much from this lovely open source database?
276
Yes! This works because MySQL doesn't update the row, if there is no change, as mentioned in docs: If you set a column to the value it currently has, MySQL notices this and does not update it. Yes!
Answer: MySQL IF() function can be used within a query, while the IF-ELSE conditional statement construct is supported to be used through FUNCTIONS or STORED PROCEDURES.
First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.
UPDATE table SET A = IF(A > 0 AND A < 1, 1, IF(A > 1 AND A < 2, 2, A)) WHERE A IS NOT NULL; you might want to use CEIL() if A is always a floating point value > 0 and <= 2
198
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
