主页 / coding / 问题 / 79207948
Accepted
Jennifer
Asked: 2024-11-20 23:39:43 +0800 CST

UPDATE 语句忽略 INNER JOIN

  • 772

我希望 SQL 语句更新有限数量的行，而不是每一行：

UPDATE Join_Test_1
SET ANumber = 3
FROM Join_Test_1 AS ST1
INNER JOIN Join_Test_2 AS ST2 ON ST1.PKID = ST2.PKID;

我希望该语句的行为与以下语句相同：

UPDATE Join_Test_1
SET ANumber = 3
FROM Join_Test_2
WHERE Join_Test_1.PKID = Join_Test_2.PKID;

UPDATE Join_Test_1
SET ANumber = 3
WHERE EXISTS (SELECT * FROM Join_Test_2 
              WHERE Join_Test_2.PKID = Join_Test_1.PKID);

UPDATE Join_Test_1
SET ANumber = 3
WHERE PKID IN (SELECT PKID FROM Join_Test_2);

使用该子句的语句WHERE仅更新匹配ANumber的列PKID。

该语句使用INNER JOIN更新表中的所有行。

为什么INNER JOIN不限制更新的行数？

是否可以重写带有的语句INNER JOIN以使用来JOIN限制更新的行数？

/* Expansive Example */
CREATE TABLE Join_Test_1 (PKID SERIAL, ANumber INTEGER);
CREATE TABLE Join_Test_2 (PKID SERIAL, ANumber INTEGER);

INSERT INTO Join_Test_1 (ANumber) VALUES (1), (1);      
INSERT INTO Join_Test_2 (ANumber) VALUES (2);       

UPDATE Join_Test_1
SET ANumber = 3
FROM Join_Test_1 AS ST1
INNER JOIN Join_Test_2 AS ST2 ON ST1.PKID = ST2.PKID; -- Updates 2

SELECT * 
FROM Join_Test_1 
ORDER BY PKID;

-- 1, 3
-- 2, 3

UPDATE Join_Test_1 
SET ANumber = 1; -- Update 2

UPDATE Join_Test_1
SET ANumber = 3
FROM Join_Test_2
WHERE Join_Test_1.PKID = Join_Test_2.PKID;

SELECT * 
FROM Join_Test_1 
ORDER BY PKID;

-- 1, 1
-- 2, 3     

UPDATE Join_Test_1 SET ANumber = 1; -- Update 2

UPDATE Join_Test_1
SET ANumber = 3
WHERE PKID IN (SELECT PKID FROM Join_Test_2);

SELECT * 
FROM Join_Test_1 
ORDER BY PKID;

-- 1, 1
-- 2, 3     

DROP TABLE IF EXISTS Join_Test_1;
DROP TABLE IF EXISTS Join_Test_2;
sql

4 个回答

  1. Best Answer

    有plpgSQL一种方法可以在连接期间更新单个表，如下所示：

    CREATE TABLE Join_Test_1 (PKID SERIAL,ANumber INTEGER);
CREATE TABLE Join_Test_2 (PKID SERIAL,ANumber INTEGER);

INSERT INTO Join_Test_1 (ANumber) VALUES (1),(1);       
INSERT INTO Join_Test_2 (ANumber) VALUES (2); 

select * from Join_Test_1;
-- 1    1
-- 2    1

select * from Join_Test_2;
-- 1    2

WITH TT AS (
    UPDATE Join_Test_1
    SET ANumber = 3
    RETURNING *
)
SELECT *
FROM TT          AS ST1
INNER JOIN 
     Join_Test_2 AS ST2
ON   ST1.PKID = ST2.PKID;
-- 1    3   1   2

SELECT * FROM Join_Test_1 ORDER BY PKID;
-- 1    3
-- 2    3

    Process-01: db<>fiddle - 加入期间更新

    Process-02: db<>fiddle - 加入期间更新

    进程-03：db<>fiddle - 正常方式

    • 1

  2. 可以说，在处理涉及多个或复杂的连接和/或条件的更新时 - 最易读和最易于维护的方法是使用MERGE INTO语法。

    MERGE INTO Join_Test_1 target
USING ( Select     t1.pkid 
        From       Join_Test_1 t1
        Inner Join Join_Test_2 t2 ON(t2.pkid = t1.pkid)
      ) src ON(src.pkid = target.pkid)
WHEN MATCHED THEN 
    UPDATE SET anumber = 3;

    SELECT * FROM Join_Test_1 ORDER BY PKID;

    结果：

    公钥 一个数字
    1 3
    2 1

    小提琴

    只需确保您的 USING 查询获取要匹配的唯一行......

    • 1

  3. 您为表设置了别名Join_Test_1，但在更新中未调用该别名。这意味着该FROM语句将被忽略。您的语句与相同UPDATE Join_Test_1 SET ANumber = 3。

    UPDATE  ST1
SET     ANumber = 3
FROM    Join_Test_1 AS ST1
INNER JOIN  Join_Test_2 AS ST2
ON          ST1.PKID = ST2.PKID;
    • 0

  4. 您的UPDATE陈述完全是错误的。目标表Join_Test_1和第二个实例之间没有建立任何连接Join_Test_1 AS ST1。您一开始就不需要 Postgres 中的目标表的第二个实例 - 除了模拟的罕见情况LEFT JOIN。请参阅：

    这会起作用：

    UPDATE join_test_1
SET    anumber = 3
FROM   join_test_2 st2 USING (pkid);

    我还简化并取消了无用的 CaMeL 大小写拼写。请参阅：

    但这仍然是一个可疑的声明。这一切都归结为中匹配行的存在join_test_2。使用连接，您可能会面临多个匹配的风险，从而产生任意结果。在带有常量赋值的简单示例中未显示（无用的测试！），但无论如何都应避免。不要多次更新同一行。

    适合此目的的正确查询是您的EXISTS变体，可以简化为：

    UPDATE join_test_1 t1
SET    anumber = 3
WHERE  EXISTS (SELECT FROM join_test_2 t2 WHERE t2.pkid = t1.pkid);
    • 0

相关问题