我有两张表:Table1,和Table2
其中Table1表类似于 A B ...
varchar(50) varchar(50)其中Table2表类似于 C D ...
varchar(50) varchar(50)其中Table1 A字段中都是唯一的,,Table2中C字段中有许多重复的, A大部分可以在C中找到
我现在想一步步来 只跟新c中与a相同的仅仅有一个的 Update Table1 set B=D
请问各位前辈如何写select distinct n.a,c.c
from Table1 as n (nolock)
inner join
Table2 as c on (c.c=n.a) where ...这样该如何UPdate到Table1中呢?
其中Table1表类似于 A B ...
varchar(50) varchar(50)其中Table2表类似于 C D ...
varchar(50) varchar(50)其中Table1 A字段中都是唯一的,,Table2中C字段中有许多重复的, A大部分可以在C中找到
我现在想一步步来 只跟新c中与a相同的仅仅有一个的 Update Table1 set B=D
请问各位前辈如何写select distinct n.a,c.c
from Table1 as n (nolock)
inner join
Table2 as c on (c.c=n.a) where ...这样该如何UPdate到Table1中呢?
update Table1 set B=b.D from Table1 a join (select C, D=max(D) from Table2 group by C having count(1)=1) b on a.A=b.C
DROP TABLE tb2CREATE TABLE tb1
(
a VARCHAR(10),
b VARCHAR(10)
)CREATE TABLE tb2
(
c VARCHAR(10),
d VARCHAR(10)
)INSERT INTO tb1
SELECT 'm','d'
UNION ALL
SELECT 'mm','dd'
UNION ALL
SELECT 'mmm','ddd'INSERT INTO tb2
SELECT 'm','e'
UNION ALL
SELECT 'm','ee'
UNION ALL
SELECT 'mm','eee'
SELECT * FROM tb1 t
SELECT * FROM tb2 tUPDATE t SET b=m.d
FROM tb1 t
INNER JOIN
( SELECT c,d,COUNT(1) OVER(PARTITION BY c) cn
FROM tb2 p
) m
ON m.c=t.a
AND m.cn=1