1

I have 2 counters. If callback data is positive number comes, I increase the likes counter by 1, if negative, I increase the dislikes counter by 1, respectively. But my current code throws a syntax error.

cursor.execute("""
UPDATE posts SET
likes_count = CASE WHEN (?) > 0 THEN
likes_count = likes_count + 1 ELSE likes_count END
dislikes_count = CASE WHEN (?) < 0 THEN
dislikes_count = dislikes_count + 1 ELSE dislikes_count END
WHERE id = ?""", (data,abs(data),))
nbk
  • 45,398
  • 8
  • 30
  • 47
vilagore
  • 11
  • 1

1 Answers1

0

Try:

cursor.execute("""
UPDATE posts SET
    likes_count = CASE WHEN (?) > 0 THEN likes_count + 1 
                       ELSE likes_count 
                  END,
    dislikes_count = CASE WHEN (?) < 0 THEN dislikes_count + 1 
                          ELSE dislikes_count 
                     END
    WHERE id = ?""", (data,data,abs(data),))

Each field update statement must resolve to a single value, your case statement cannot do anything except return a single value. Simply remove the assignment inside your case statement, and the rest is valid.

THEN likes_count = likes_count + 1 ELSE …
Should be THEN likes_count + 1 ELSE …

Then you must separate each field update statement with a comma ,.

Finally, note that you must pass through a parameter for each ?. There are 3 ? in this script, so you'll need 3 inputs.

Chris Schaller
  • 13,704
  • 3
  • 43
  • 81
  • unfortunately this does not work. I copied this code and got the following error, `sqlite3.OperationalError: near "dislikes_count": syntax error` – vilagore Dec 12 '19 at 00:36
  • Sorry, forgot the comma :), I've updated the response – Chris Schaller Dec 12 '19 at 00:41
  • Yes, I too figured out this via following question https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server Just tried to escape double case and implement through `else` (or at least to escape double else), but it is both impossible :) – vilagore Dec 12 '19 at 01:04
  • I don't have anything setup to test this script, but looks like you need to provide an additional input parameter as well – Chris Schaller Dec 12 '19 at 01:15