从具有多个 WHERE 条件的另一个表进行更新

发布于 2025-01-14 04:26:28 字数 1453 浏览 1 评论 0原文

在Postgres 9.5中，我想使用Postgres的dblink连接到另一个数据库，获取数据，然后使用它们来更新另一个表。

-- connect to another DB, get data from table, put it in a WITH 
WITH temp_table  AS 
( 
    SELECT r_id, descr,  p_id 
    FROM 
    dblink('myconnection', 
           'SELECT 
            r_id, descr,   p_id 
            FROM table
            WHERE table.p_id 
            IN (10,20);'
    ) 
    AS tempTable(r_id integer, descr text, p_id integer)
) 

-- now use temp_table  to update

UPDATE anothertable   
SET     
descr =temp_table.descr 
FROM anothertable    AS x
INNER JOIN temp_table 
ON  
x.r_id = temp_table.r_id 
AND 
x.p_id = temp_table.p_id 
AND 
x.p_id IN (2)  ;

dblink 工作正常，如果我在 UPDATE 之前执行 select * from temp_table ，它就会有数据。

问题在于 UPDATE 本身。它运行时没有错误，但它从未真正更新表。

我尝试将 UPDATE 更改为：

UPDATE anothertable   
SET     
descr =temp_table.descr 
FROM anothertable    AS x , temp_table
WHERE x.r_id = temp_table.r_id 
AND 
x.p_id = temp_table.p_id 
AND 
x.p_id IN (2)  ;

与上面相同：运行时没有错误，但它实际上从未更新表。

我还尝试将 UPDATE 更改为：

UPDATE anothertable   
INNER JOIN temp_table
ON x.r_id = temp_table.r_id 
    AND 
    x.p_id = temp_table.p_id 
    AND 
    x.p_id IN (2) 
    SET descr =temp_table.descr

但我得到：

错误：“INNER”SQL 状态或附近的语法错误：42601 人物：1894

我该如何修复这个问题才能真正更新？

原文

In Postgres 9.5, I want to connect to another DB using Postgres' dblink, get data and then use them to update another table.

-- connect to another DB, get data from table, put it in a WITH 
WITH temp_table  AS 
( 
    SELECT r_id, descr,  p_id 
    FROM 
    dblink('myconnection', 
           'SELECT 
            r_id, descr,   p_id 
            FROM table
            WHERE table.p_id 
            IN (10,20);'
    ) 
    AS tempTable(r_id integer, descr text, p_id integer)
) 

-- now use temp_table  to update

UPDATE anothertable   
SET     
descr =temp_table.descr 
FROM anothertable    AS x
INNER JOIN temp_table 
ON  
x.r_id = temp_table.r_id 
AND 
x.p_id = temp_table.p_id 
AND 
x.p_id IN (2)  ;

dblink works fine and if I do select * from temp_table before the UPDATE, it has data.

The issue is the UPDATE itself. It runs with no errors, but it never actually updates the table.

I tried changing the UPDATE to:

UPDATE anothertable   
SET     
descr =temp_table.descr 
FROM anothertable    AS x , temp_table
WHERE x.r_id = temp_table.r_id 
AND 
x.p_id = temp_table.p_id 
AND 
x.p_id IN (2)  ;

Same as above: runs with no errors, but it never actually updates the table.

I also tried to change the UPDATE to:

UPDATE anothertable   
INNER JOIN temp_table
ON x.r_id = temp_table.r_id 
    AND 
    x.p_id = temp_table.p_id 
    AND 
    x.p_id IN (2) 
    SET descr =temp_table.descr

But I get:

ERROR: syntax error at or near "INNER" SQL state: 42601
Character: 1894

How can I fix this to actually update?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

指尖上得阳光 2025-01-21 04:26:28

不要在UPDATE的FROM子句中重复目标表：

WITH temp_table AS ( ... )

UPDATE anothertable x
SET    descr = t.descr 
FROM   temp_table t
WHERE  x.r_id = t.r_id 
AND    x.p_id = t.p_id 
AND    x.p_id IN (2);

或者简化：