Re: Comibining UPDATE ... SET ... FROM (SELECT ...) with a JOIN

Thank you Brian and others, but -

On Fri, Sep 9, 2016 at 5:22 PM, Brian Dunavant <brian(at)omniti(dot)com> wrote:

> I'm making the assumption that you only have one ip/user in words_users.
>
> with lockrow as (
> SELECT g.gid, u.ip
> FROM words_games g join words_users u
> ON (g.player1 = u.uid)
> WHERE g.finished IS NULL
> AND g.player1 <> in_uid
> AND g.played1 IS NOT NULL
> AND g.player2 IS NULL
> LIMIT 1
> FOR UPDATE SKIP LOCKED
> ), do_the_update as (
> UPDATE words_games g1
> SET player2 = in_uid
> FROM lockrow g2
> WHERE g1.gid = g2.gid
> RETURNING g1.gid, g1.player2
> )
> select m.gid into out_gid, u.ip into out_uip
> from do_the_update m
> join lockrow u on (gid)
> ;
>
> The general idea being lock the row in the first CTE, update it in the
> second, returning your values, and then query against those in the
> final select to get the ip. If it didn't update anything, you'll get
> no results.
>

unfortunately, the above query does not seem to ensure, that players with
same ip can not join the same game, which is actually my question...

But thanks for showing the CTE for UPDATE ... RETURNING - that is probably
the way to go for me

Regards
Alex