SQL 用一个表的数据更新另一张表
用表B的数据(B1列,B2)更新表A的A1,A2列
SQL Server:
update A SET A.A1 = b.B1,A.A2=B.A2 FROM A ,B WHERE A.ID1 = B.ID1 and A.ID2 = B.ID2;
Access:
update A, B set A.A1 = B.B1,A.A2=B.B2 where A.ID1 = B.ID1 and A.ID2 = B.ID2;
--或
update A INNER JOIN B ON A.ID1 = B.ID1 AND A.ID2= B.ID2 SET A.A1 = B.B1,A.A2=B.B2;
通过一条SQL语句一次更新多条数据.SQL语句写法略有不同,如下:
方法一:
Update T1
set dc=(select dc1 from t2 where T1.A=T2.A1 AND T1.B=T2.B1)
WHERE T1.AID in (
SELECT AID FROM T1
INNER JOIN T2 ON t1.A=T2.A1 AND T1.B=T2.B1
)
注:在我标我红色的in地方如果把'in'换成'='将出错,但在下面的B种写法里就可以换成'=',变成C方法写法.
方法二:
Update T1
set dc=(select dc1 from t2 where T1.A=T2.A1 AND T1.B=T2.B1)
WHERE T1.AID in (
SELECT T1.AID FROM T2
WHERE t1.A=T2.A1 AND T1.B=T2.B1
)
方法三:
Update T1
set dc=(select dc1 from t2 where T1.A=T2.A1 AND T1.B=T2.B1)
WHERE T1.AID = (
SELECT T1.AID FROM T2
WHERE t1.A=T2.A1 AND T1.B=T2.B1
)
方法四:
Update T1
set dc=(select dc1 from T2 where T1.A=T2.A1 AND T1.B=T2.B1)
WHERE T1.A = (
SELECT T1.A FROM T2
WHERE T1.A=T2.A1 AND T1.B=T2.B1
)
方法五:
Update T1
set dc=(select dc1 from T2 where T1.A=T2.A1 AND T1.B=T2.B1)
WHERE T1.A = (
SELECT T2.A1 FROM T2
WHERE T1.A=T2.A1 AND T1.B=T2.B1
)
方法六:
Update T1
set dc=(select dc1 from T2 where T1.A=T2.A1 AND T1.B=T2.B1)
WHERE T1.A in (
SELECT T2.A1 FROM T2
WHERE T1.A=T2.A1 AND T1.B=T2.B1
)
注:如果在上面方法改写成如下的写法将会出错。
Update T1
set dc=(select dc1 from T2 where T1.A=T2.A1 AND T1.B=T2.B1)
WHERE T1.A in (
SELECT T1.A FROM T1
INNER JOIN T2 ON t1.A=T2.A1 AND T1.B=T2.B1
)
此种方法将会更新掉所有符合T1.A in ()这个条件的数据,所以是不正确的, 所以不能这样写。
方法七:
Update T1
set dc=(select dc1 from T2 where T1.A=T2.A1 AND T1.B=T2.B1 AND T1.DC=T2.DC1)
FROM T1, T2
WHERE T1.A=T2.A1 AND T1.B=T2.B1
方法八:
Update T1
set T1.dc=T2.dc1
FROM T1
INNER JOIN T2 ON T1.A=T2.A1 AND T1.B=T2.B1
WHERE t1.A=T2.A1 AND T1.B=T2.B1
方法九:
Update T1
set T1.dc=T2.dc1
FROM T1, T2
WHERE T1.A=T2.A1 AND T1.B=T2.B1
请特别注意以上方法中我用红色标记上的代码写法。
总节:虽然是一条简单的更新语名但也有多种写法,且每种写法各有优缺点。
大家是否还有更简单的写法或者更容易理解的写法?如果有请提出来一起讨论,谢谢!
说明测试环境:以上代码在MSSQL2005中验证是可行的.
注:此处我用灰色标记的部分可以去掉。