用联接更新postgres [英] postgres update with join

问题描述

我正在尝试使用ht中的数据更新表tr。两者都有几乎相同的列。
因此要测试，我运行了此查询。

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

给出129行
确认一切正常，然后我将更新查询作为->

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

查询成功返回：4134行受影响

Query returned successfully: 4134 rows affected

有什么可以指导我哪里出了问题以及如何解决。

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

推荐答案

这是Postgres有点复杂。但是，我怀疑你的逻辑。似乎右外部联接是不正确的，因为您正在检查第一个表不是 NULL 。因此，这将似乎来捕获您的逻辑：

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 );

where 子句甚至是多余的，因为由于 join 条件，该值不能为 NULL 。无论如何，这都行不通，但它确实指向正确的解决方案。

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.

在Postgres中，更新中的表不能位于 FROM 子句中，除非您的意图是自我联接。因此，请尝试以下版本：

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;

但是，此更新没有意义。我怀疑您希望这些值来自 ht ， NOT tr 是一场比赛。如果是这样，这是最简单的方法：

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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！