34,593
社区成员
发帖
与我相关
我的任务
分享
DROP TABLE tb1
DROP TABLE tb2
CREATE 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 t
UPDATE 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
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
update a set a.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
update a set a.B=0 from Table1 a join (select C from Table2 group by C having count(1)=1) b on a.A=b.C