postgres update with join

Question

I am trying to update table tr with data from ht. Both have almost same columns. So to test I ran the this query.

SELECT * FROM tr a
RIGHT OUTER JOIN ht b
USING (date, name, ft )
WHERE ft IS NOT NULL

Gives 129 rows Check it was ok, then I ran the update query as->

UPDATE tr
SET (o_t, g_v, br, s_t, st, c_d, n_r, n_a, cd_id) 
    = (a.o_t, a.g_v, a.br, a.s_t, a.st, a.c_d, a.n_r, a.n_a, 
    a.cd_id)
FROM tr a
RIGHT OUTER JOIN ht b
USING (date, name, ft )
WHERE a.ft IS NOT NULL

Query returned successfully: 4134 rows affected

Can some on guide me what went wrong and how to go about it.

You have right outer join on first, left outer join on second. That might have something to do with it? — Sami Kuhmonen
– Sami Kuhmonen, Commented Aug 12, 2015 at 10:21
It's more robust to just paste in the code that you're using, otherwise you're inviting all kinds of typos that look like the problem but are not. — David Aldridge
– David Aldridge, Commented Aug 12, 2015 at 10:37
Your query doesn't make sense. Either a single row matches and hence the values do not change. Do you intend for the values to come from b? — Gordon Linoff
– Gordon Linoff, Commented Aug 12, 2015 at 10:45

Gordon Linoff · Accepted Answer · 2015-08-12 10:47:09Z

This is a bit complicated in Postgres. However, I question your logic. It would seem that a right outer join is not correct, because you are checking that the first table is not NULL. So this would seem to capture your logic:

UPDATE tr
SET (o_t, g_v, br, s_t, st, c_d, n_r, n_a, cd_id) 
    = (a.o_t, a.g_v, a.br, a.s_t, a.st, a.c_d, a.n_r, a.n_a, 
    a.cd_id)
FROM tr a JOIN
     ht b
     USING (date, name, ft );

The where clause is even redundant, because the value cannot be NULL due to the join condition. In any case, this doesn't work, but it does point to the right solution.

In Postgres, the table in the update cannot be in the FROM clause, unless your intention is a self join. So, try this version:

UPDATE tr
SET (o_t, g_v, br, s_t, st, c_d, n_r, n_a, cd_id) 
    = (a.o_t, a.g_v, a.br, a.s_t, a.st, a.c_d, a.n_r, a.n_a, 
    a.cd_id)
FROM tr a JOIN
     ht b
     USING (date, name, ft )
WHERE tr.date = a.date and tr.name = a.name and tr.ft = a.ft;

However, this update doesn't make sense. I suspect that you want the values to come from ht, NOT tr, when there is a match. If so, this is the simplest way:

UPDATE tr
    SET (o_t, g_v, br, s_t, st, c_d, n_r, n_a, cd_id) 
        = (a.o_t, a.g_v, a.br, a.s_t, a.st, a.c_d, a.n_r, a.n_a, 
        a.cd_id)
    FROM ht a
    WHERE tr.date = a.date and tr.name = a.name and tr.ft = a.ft;

Collectives™ on Stack Overflow

postgres update with join

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related