我有这些表和值:
Table1 Table2
------------------ --------
ID | CREATED_BY ID | NAME
------------------ --------
1 | 1 | aaa
2 | 2 | bbb
3 | 3 | ccc
4 | 4 | ddd
Table1_2_link
--------------------------
ID | TABLE1_ID | TABLE2_ID
--------------------------
1 | 1 | 1
2 | 2 | 2
3 | 3 | 3
4 | 3 | 4
我想用表2中的名称值更新表1中的created_by列,其中表1_2_link只有一对(Table1_ID,Table2_ID(。
更新后的结果必须是:
Table1
------------------
ID | CREATED_BY
------------------
1 | aaa
2 | bbb
3 |
4 |
有没有一种方法可以通过一个简单的SQL查询做到这一点?
您需要一个带有update
:的join
update table1 t1
set created_by = t2.name
from (select t12.TABLE1_ID, max(t12.TABLE2_ID) as TABLE2_ID
from Table1_2_link t12
group by t12.TABLE1_ID
having count(*) = 1
) t12 join
table2 t2
on t12.TABLE2_ID = t2.id
where t12.TABLE1_ID = t1.id;
Table1_2_link
上的子查询查找只有一行的ID。当只有一行时,MAX()
返回该行中TABLE2_ID
的值。
请尝试以下查询:
update Table1 as tt1
SET CREATED_BY = tt1.NAME
FROM(
SELECT t1.ID, t3.NAME
FROM Table1 t1
INNER JOIN Table1_2_link t2 ON t1.id = t2.TABLE1_ID
INNER JOIN Table2 t3 ON t2.TABLE2_ID = t3.ID)
T
where T.ID = tt1.ID