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, Aug 12 '15 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, Aug 12 '15 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, Aug 12 '15 at 10:45

score 0 · Accepted Answer · answered Aug 12 '15 at 10:47

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;

postgres update with join

1 Answers1

Linked