MySQL -在MySQL更新或选择查询时使用If

How do I update a table and set different values upon the condition evaluating to True.

如何更新表并在条件求值为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?

我已经在过程和函数中看到过CASE表达式和IF表达式，但是我想在一个简单的update/select语句中使用它。我对这个可爱的开源数据库期望过高了吗?

3 个解决方案

#1

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

如果A总是一个浮点值>和<= 2，您可能需要使用CEIL()

#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:

虽然您当然可以使用MySQL的IF()控制流函数，如dbemerlin的答案所示，但我怀疑对于读者(例如您自己，以及将来可能会使用您的代码的开发人员)来说，使用CASE表达式可能会更清楚一些:

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:

当然，在这个特定的示例中，在ELSE语句中设置a本身有点浪费——最好完全通过WHERE子句从更新中过滤这些条件:

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).

(不等式包含的A不是零)

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):

或者，如果您希望间隔是关闭的，而不是打开的(请注意，这将把值设置为0到1，如果不需要，可以在WHERE子句中显式地过滤此类情况，或者在条件为时添加更高的优先级):

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:

不过，正如dbmerlin所指出的，对于这种特殊情况，您可以考虑使用CEIL()来代替:

UPDATE Table SET A = CEIL(A) WHERE A BETWEEN 0 AND 2

#3

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

下面是一个基于另一个表的比较更新表的查询。如果表b中没有找到记录，则将“活动”值更新为“n”。如果找到，则将值设置为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.

希望这能帮助别人。

#1

UPDATE table
SET A = IF(A > 0 AND A < 1, 1, IF(A > 1 AND A < 2, 2, A))
WHERE A IS NOT NULL;