所以這是我第一次想對具有多個連接的查詢進行更新。
資料庫 = Postgres v.10
這是我到目前為止嘗試過的：
update table1 t1 set t4.datum = t1.datum
from table1 t1
inner join table3 t3 on t3.id = t1.id
inner join table4 t4 on t4.id = t3.t4_id
where t3.id = 550 and t4.scale is not null and t4.datum is null
錯誤：SQL 錯誤 [42712]：錯誤：多次指定表名“t1”
下一次嘗試：
update table1 t1 set t4.datum = t.datum
from table1 t
inner join table3 t3 on t3.id = t.id
inner join table4 t4 on t4.id = t3.t4_id
where t3.id = 550 and t4.scale is not null and t4.datum is null
錯誤：SQL 錯誤 [42703]：錯誤：關系“table1”的列“t4”不存在位置：28
最后嘗試：
update table1 t1 set t4.datum = t.datum
from table1 t
inner join table3 t3 on t3.id = t.id
inner join table4 t4 on t4.id = t3.t4_id
where t1.id = t.id and t3.id = 550 and t4.scale is not null and t4.datum is null
錯誤：SQL 錯誤 [42703]：錯誤：關系“table1”的列“t4”不存在位置：28
我究竟做錯了什么？
uj5u.com熱心網友回復：
您不應在 FROM 子句中重復 UPDATE 的目標表。所以有點像。分配set t4.datum = t.datum似乎也錯了。如果要更新table1，則無法t4在作業的左側參考。此外，目標列不能在 SET 部分內被“表限定”（因為很清楚哪個表的列是指）
所以我認為你正在尋找這樣的東西：
update table1 t1
set datum = t4.datum
from table3 t3
inner join table4 t4 on t4.id = t3.t4_id
where t1.id = t3.id
and t3.id = 550
and t4.scale is not null
and t4.datum is null
uj5u.com熱心網友回復：
在 FROM 子句中參考表的別名，然后在整個程序中使用它。我想我正確地編輯了這個：
update t1 set t4.datum = t1.datum
from table1 t1
inner join table3 t3 on t3.id = t1.id
inner join table4 t4 on t4.id = t3.t4_id
where t3.id = 550 and t4.scale is not null and t4.datum is null;
uj5u.com熱心網友回復：
我認為您的問題是因為想t4.datum從 table更新table1。
您應該將設定列更改t4.datum = t1.datum為t1.datum = t4.datum因為您想要更新table1（更新查詢：）update table1 t1而t4.datum不是參考table1
應該像下面這樣更改查詢（如果你想要更新table1）：
update table1 t1 set t1.datum = t4.datum
from table1 t
inner join table3 t3 on t3.id = t.id
inner join table4 t4 on t4.id = t3.t4_id
where t1.id = t.id and t3.id = 550 and t4.scale is not null and t4.datum is null
已編輯
查詢更新table4：
update table4 u_t4 set datum = t1.datum
from table1 t1
inner join table3 t3 on t3.id = t1.id
inner join table4 t4 on t4.id = t3.t4_id
where t4.id = u_t4.id and t3.id = 550 and t4.scale is not null and t4.datum is null
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/374320.html
標籤：PostgreSQL的 加入
上一篇：MySQL從另一個表加入多個ID