sql - 使用 JOIN 有条件地更新 SQL 表

Question

假设您有表格:

CREATE TABLE TABLE1 (ID INTEGER PRIMARY KEY, VAL INT, CONDITION INT);
CREATE TABLE TABLE2 (ID INTEGER PRIMARY KEY, VAL INT, CONDITION INT);

有数据:

INSERT INTO TABLE1 VALUES (0, 0, 100);

和

INSERT INTO TABLE2 VALUES
(0, 2, 100),
(1, 1, 100),
(3, 3, 100)

查询后 TABLE1 中会出现什么？为什么？

UPDATE TABLE1 SET VAL = SOURCE.VAL 
FROM TABLE2 SOURCE 
WHERE TABLE1.CONDITION = SOURCE.CONDITION

我得到了:

0;2;100

这是否意味着 RDBMS 多次执行 UPDATE 操作而我只看到最终结果？或者它只更新一次 VAL？

解释:

"Update on table1  (cost=270.68..562.65 rows=18818 width=24)"
"  ->  Merge Join  (cost=270.68..562.65 rows=18818 width=24)"
"        Merge Cond: (table1.condition = source.condition)"
"        ->  Sort  (cost=135.34..140.19 rows=1940 width=14)"
"              Sort Key: table1.condition"
"              ->  Seq Scan on table1  (cost=0.00..29.40 rows=1940 width=14)"
"        ->  Sort  (cost=135.34..140.19 rows=1940 width=14)"
"              Sort Key: source.condition"
"              ->  Seq Scan on table2 source  (cost=0.00..29.40 rows=1940 width=14)"

我使用了 PostgreSQL。

Answer 1

来自documentation :

a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

所以您看到的行为正是应该发生的。