我有兩張桌子，我想UPDATE根據另一張桌子的值來一張桌子。在以下SO-post的幫助下，我撰寫了一個查詢：
query = f""" UPDATE table1
SET goal =
(SELECT table2.goal FROM table2
WHERE player = table2.player
AND opponent = table2.opponent
AND date = table2.date
AND competition = table2.competition
AND score = table2.score """
當我執行查詢時，每一行table1都會受到相同值的影響goal。但是，所需的程序是查詢逐行檢查是否存在匹配的行，如果有，則更新列目標。我究竟做錯了什么？
uj5u.com熱心網友回復：
您必須將子查詢與要更新的表相關聯：
UPDATE table1 AS t1
SET goal = (
SELECT t2.goal
FROM table2 AS t2
WHERE t2.player = t1.player
AND t2.opponent = t1.opponent
AND t2.date = t1.date
AND t2.competition = t1.competition
AND t2.score = t1.score
);
要么：
UPDATE table1 AS t1
SET goal = (
SELECT t2.goal
FROM table2 AS t2
WHERE (t2.player, t2.opponent, t2.date, t2.competition, t2.score) =
(t1.player, t1.opponent, t1.date, t1.competition, t1.score)
);
請注意，如果 in 中的行與 in中的table1任何行都不匹配table2，則該列將更新為null。
如果在這種情況下您不希望更新列，也可以使用COALESCE()：
UPDATE table1 AS t1
SET goal = COALESCE((
SELECT t2.goal
FROM table2 AS t2
WHERE (t2.player, t2.opponent, t2.date, t2.competition, t2.score) =
(t1.player, t1.opponent, t1.date, t1.competition, t1.score)
), goal);
如果您的 SQLite 版本是 3.33.0 ，您可以使用以下UPDATE..FROM語法：
UPDATE table1 AS t1
SET goal = t2.goal
FROM table2 AS t2
WHERE (t2.player, t2.opponent, t2.date, t2.competition, t2.score) =
(t1.player, t1.opponent, t1.date, t1.competition, t1.score);
uj5u.com熱心網友回復：
據我了解，如果 table2 具有相同的值，此查詢只會影響 table1。您是否要檢查是否有任何行相同然后更新目標值？
您可以使用 OR，而不是使用 AND。如果任何值相似，此修改將確保查詢將通過。
query = f""" UPDATE table1
SET goal =
(SELECT table2.goal FROM table2
WHERE player = table2.player
OR opponent = table2.opponent
OR date = table2.date
OR competition = table2.competition
OR score = table2.score )"""
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/446879.html