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?
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
Whilst you certainly can use MySQL's IF() control flow function as demonstrated by dbemerlin's answer, I suspect it might be a little clearer to the reader (i.e. yourself, and any future developers who might pick up your code in the future) to use a CASE expression instead:
UPDATE Table
SET A = CASE
WHEN A > 0 AND A < 1 THEN 1
WHEN A > 1 AND A < 2 THEN 2
ELSE A
END
WHERE A IS NOT NULL
Of course, in this specific example it's a little wasteful to set A to itself in the ELSE clause—better entirely to filter such conditions from the UPDATE, via the WHERE clause:
UPDATE Table
SET A = CASE
WHEN A > 0 AND A < 1 THEN 1
WHEN A > 1 AND A < 2 THEN 2
END
WHERE (A > 0 AND A < 1) OR (A > 1 AND A < 2)
(The inequalities entail A IS NOT NULL).
Or, if you want the intervals to be closed rather than open (note that this would set values of 0 to 1—if that is undesirable, one could explicitly filter such cases in the WHERE clause, or else add a higher precedence WHEN condition):
UPDATE Table
SET A = CASE
WHEN A BETWEEN 0 AND 1 THEN 1
WHEN A BETWEEN 1 AND 2 THEN 2
END
WHERE A BETWEEN 0 AND 2
Though, as dbmerlin also pointed out, for this specific situation you could consider using CEIL() instead:
UPDATE Table SET A = CEIL(A) WHERE A BETWEEN 0 AND 2
if() statement solution offered by @dbemerlin requires nesting past two conditions, which will make the logic even harder to read and understand.Here's a query to update a table based on a comparison of another table. If record is not found in tableB, it will update the "active" value to "n". If it's found, will set the value to NULL
UPDATE tableA
LEFT JOIN tableB ON tableA.id = tableB.id
SET active = IF(tableB.id IS NULL, 'n', NULL)";
Hope this helps someone else.
