Grouping conditions with parentheses not working

Question

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?

Your record fails the test `scoreT2 != 0`. Of course it is not modified. — Gordon Linoff, Feb 09 '15 at 22:39
*Always* include the exact table definition with a question like this. — Erwin Brandstetter, Feb 10 '15 at 00:27

score 0 · Answer 1 · answered Feb 09 '15 at 22:43

From your description you want

 NOT("scoreT1" - "scoreT2" == 1 OR ("scoreT1" == 1 AND "scoreT2" == 0))

Which logically would be

 "scoreT1" - "scoreT2" != 1 AND ("scoreT1" != 1 OR "scoreT2" != 0)

When you commute a NOT operator into a compound comparison you flip ANDs to ORs and vice-versa.

score 0 · Accepted Answer · edited May 23 '17 at 12:27

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:

How do I (or can I) SELECT DISTINCT on multiple columns?

Grouping conditions with parentheses not working

2 Answers2