sql - 甲骨文 SQL : Update a table column using another table referenced by 3rd table in-between

Question

我目前有如下三个表:

表 1

book_id    margin
-------------------
   b1       10
   b2       20
   b3       30

表2

t2_id   book_id     author_id
-----------------------------
  1         b1        100
  2         b2        200
  3         b3        300

表 3

author_id    revenue
----------------------
   100          0
   200          0
   300          0

我正在尝试更新表 3 上的收入，其中书的相应作者(在表 3 上)的利润为表 1 的 50%。结果应将表 3 更新为:

author_id    revenue
----------------------
   100          10
   200          20
   300          30

我可以从另一个表中更新值，如果它们直接与一个公共(public)键链接在一起，我很难在中间引用另一个表来得到答案:(

我试过:

UPDATE table3 t3 SET revenue = 
(SELECT t1.margin FROM table1 t1 WHERE
(SELECT t1.book_id FROM table1 t1 JOIN table2 t2 ON t1.book_id = t2.book_id) = 
(SELECT author_id FROM table3 t3 JOIN table2 t2 ON t3.authoer_id = t2.author_id));

谢谢

Answer 1

使用 join 更新

update 
(
SELECT table3.revenue as OLD, table1.margin as NEW
FROM table1 INNER JOIN table2 on table1.book_id=table2.book_id
inner join table3 on table2.author_id=table3.author_id
)t set t.old=t.new