SQL Update for cases

Question

I'm trying to update a column after I join two tables. If one column matches another I want to change another to 'W' and if it is already blank/NULL I want to leave it, otherwise I want to change it to 'L'. Here is what I have:

UPDATE user_games 
LEFT JOIN games ON user_games.game_key = games.game_id 
SET user_games.result = CASE 
                            WHEN user_games.pick = games.winner then 'W';
                            WHEN games.winner = NULL then NULL;
                            ELSE 'L';
                        END;

I'm getting this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 4

I'm using Server version: 10.4.20-MariaDB

Sample data and desired results would be quite helpful. – Gordon Linoff Sep 15 '21 at 11:35 — Gordon Linoff, Sep 15 '21 at 11:35

ignatiusme · Answer 1 · 2021-10-19T09:37:44.563

0

Is the query below solve your issues ?

update  a
set a.result = 
CASE 
    WHEN a.pick = b.winner then 'W'
    WHEN b.winner = NULL then NULL
    ELSE 'L'
END
from user_games  a
LEFT JOIN games b ON a.game_key = b.game_id

edited Oct 19 '21 at 09:37

answered Sep 15 '21 at 05:11

ignatiusme

26
3

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-ask). – Community Sep 15 '21 at 05:41

score 0 · Answer 2 · answered Sep 15 '21 at 05:16

TRY THIS: you have to write a specific condition for L because in ELSE it can be updated in many conditions. Also, use IS NULL to check the NULL value in the column instead of = NULL.

UPDATE user_games 
LEFT JOIN games ON user_games.game_key = games.game_id 
SET user_games.result = 
            CASE 
                WHEN user_games.pick = games.winner THEN 'W'
                WHEN games.winner IS NOT NULL AND user_games.pick <> games.winner THEN 'L'
                ELSE NULL
            END;

juergen d · Accepted Answer · 2021-09-15T05:05:24.287

-1

; is used to indicate the end of a query and should not be used in between.
Also always use IS to compare with null instead of =

SET user_games.result = 
                  CASE WHEN games.winner IS NULL OR games.winner = '' then NULL
                       WHEN user_games.pick = games.winner then 'W' 
                       ELSE 'L'
                  END;

edited Sep 15 '21 at 05:05

answered Sep 15 '21 at 04:38

juergen d

201,996
37
293
362

Thank you! It is halfway working; however, I'm still seeing the user_games.pick column getting updated with 'L' when the games.winner is NULL.. it is empty, would it be '' instead of NULL? – Geomon Sep 15 '21 at 05:05
Thank you so much @juergend!!! It's working perfectly now. The columns were '' instead of NULL. Greatly appreciated! – Geomon Sep 15 '21 at 05:13
@juergend I don't know why you have a negative score for the solution. You replied first and updated your response with details that addressed the issue. Thank you for your help! – Geomon Sep 16 '21 at 08:18

SQL Update for cases

3 Answers3