Search code examples
sqlpostgresqlboolean-logicoperator-precedence

Grouping conditions with parentheses not working

I am trying to use an SQL query in Postgres that looks like this:

UPDATE gamebet 
SET points = 15 
WHERE game = 8 
  AND "scoreT1" > "scoreT2" 
  AND "scoreT1" - "scoreT2" != 1 
  AND ("scoreT1" != 1 AND "scoreT2" != 0)

It should update the scores of some bets where scoreT1 is bigger than scoreT2, but there are some rows that should not be updated. And that is when scoreT1 - scoreT2 = 1 And if both scoreT1 = 1 and scoreT2 = 0, but it should only apply if both of these conditions are met. And somehow the parenthesis are not applied. Since e.g. this record is not modified:

scoreT1 = 3
scoreT2 = 0

This record should be updated, but since one of the conditions is scoreT2 != 0 it is not updated. How can I group the last conditions that they need to be met together?

Solution

  • As long as NULL values are not ruled out, your description must be translated to something like this:

    UPDATE gamebet 
SET    points = 15 
WHERE  game = 8 
AND   "scoreT1" > "scoreT2" 
AND  ("scoreT1" = "scoreT2" + 1 AND "scoreT1" = 1) IS NOT TRUE;

    The additional condition "scoreT2" = 0 follows logically from the first two and is redundant.

    If "scoreT1" and "scoreT2" are defined NOT NULL, you can further simplify:

    ...
AND   NOT ("scoreT1" = "scoreT2" + 1 AND "scoreT1" = 1)

    Or with inverted logic:

    ...
AND   ("scoreT1" <> "scoreT2" + 1 OR "scoreT1" <> 1)

    Read about Logical Operators, Comparison Operators and Operator Precedence in the manual.

    And if points can already be 15, it pays to add another predicate to avoid empty updates:

    AND points IS DISTINCT FROM 15

    Or, with NULL values ruled out:

    AND points <> 15

    Details: