CODEWITHSUNDEEP
sqlpostgresqlsql-update
Alexander Farber
Alexander Farber

Reputation: 22958

UPDATE ... FROM different column depending on a condition

I have prepared a simple SQL Fiddle for my question.

In PostgreSQL 10 user ids are stored in words_users table, but their names are stored in words_social table:

CREATE TABLE words_users (
        uid     SERIAL PRIMARY KEY
);

CREATE TABLE words_social (
        sid     text     NOT NULL,
        social  integer  NOT NULL CHECK (0 < social AND social <= 64),
        given   text     NOT NULL CHECK (given ~ '\S'),
        uid     integer  NOT NULL REFERENCES words_users ON DELETE CASCADE,
        PRIMARY KEY(sid, social)
);

INSERT INTO words_users (uid) VALUES (1), (2);

INSERT INTO words_social (sid, social, given, uid) VALUES 
('1111', 10, 'Alice', 1), 
('2222', 20, 'Bob', 2);

Two-player games are stored in the following table, with chat1 and chat2 columns holding number of new chat messages for player1 or player2 (here Alice and Bob both have 1 new message waiting in the game #100):

CREATE TABLE words_games (
        gid SERIAL PRIMARY KEY,
        player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL CHECK (player1 <> player2),
        player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,
        chat1 integer NOT NULL,
        chat2 integer NOT NULL
);

INSERT INTO words_games (gid, player1, player2, chat1, chat2) 
VALUES (100, 1, 2, 1, 1);

When a user reads her chat messages, I would like to reset the corresponding column (chat1 or chat2) back to 0.

And I can not use uid to identify the user, I have to go via sid and social.

So I am trying to reset the number of new chat messages for Bob in game #100:

UPDATE words_games g
SET    CASE WHEN s.uid = g.player1 THEN chat2 = 0 ELSE chat1 = 0 END
FROM   words_social s
WHERE  g.gid    = 100
AND    s.social = 20
AND    s.sid    = '2222'
AND    s.uid IN (g.player1, g.player2);

but get a syntax error, because CASE is not allowed there.

Does anybody please have a good suggestion here?

The background of my question is that I have already a working stored function in pl/pgSQL but would like to switch to pure SQL for better performance.

Upvotes: 0

Views: 56

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can do this with two separate set clauses:

UPDATE word_games g
    SET chat2 = (CASE WHEN s.uid = g.player1 THEN 0 ELSE chat2 END),
        chat1 = (CASE WHEN s.uid = g.player1 THEN chat1 ELSE 0 END)
    . . .

Upvotes: 1

Related Questions