b表的字段:bid,newdata,其中bid是主键
a表的字段:aid,bid,mydata
现在需要将a表mydata用b表的newdata代替。
例如:
b表数据:
1 200
2 300
3 400
........a表的数据:
1 1 null
2 1 123
3 3 0
4 3 456执行完语句后,
b表数据不变,a表的数据如下:
1 1 200
2 1 200
3 3 400
4 3 400请问这样的语句应该如何写?
a表的字段:aid,bid,mydata
现在需要将a表mydata用b表的newdata代替。
例如:
b表数据:
1 200
2 300
3 400
........a表的数据:
1 1 null
2 1 123
3 3 0
4 3 456执行完语句后,
b表数据不变,a表的数据如下:
1 1 200
2 1 200
3 3 400
4 3 400请问这样的语句应该如何写?
select a.aid,a.bid,b.newdata from a left join b on a.bid=b.bid
set mydata = b.newdata
from a , b
where a.bid = b.bid
set mydata = b.newdata
from a , b
where a.bid = b.bid
update ta set mydata = tb.newdata from tb where ta.bid = tb.bid
IF OBJECT_ID('[b]') IS NOT NULL
DROP TABLE [b]
GO
CREATE TABLE [b] ([bid] [int],[newdata] [int])
INSERT INTO [b]
SELECT '1','200' UNION ALL
SELECT '2','300' UNION ALL
SELECT '3','400'--> 生成测试数据表: [a]
IF OBJECT_ID('[a]') IS NOT NULL
DROP TABLE [a]
GO
CREATE TABLE [a] ([aid] [int],[bid] [int],[mydata] [int])
INSERT INTO [a]
SELECT '1','1',NULL UNION ALL
SELECT '2','1','123' UNION ALL
SELECT '3','3','0' UNION ALL
SELECT '4','3','456'--SELECT * FROM [b]
--SELECT * FROM [a]-->SQL查询如下:UPDATE a SET mydata=(SELECT newdata FROM b WHERE a.bid=b.bid)SELECT * FROM a
/*
aid bid mydata
----------- ----------- -----------
1 1 200
2 1 200
3 3 400
4 3 400(4 行受影响)
*/
uodate a set mydate=b.newdate from a,b where a.bid=b.bid
UPDATE a SET a.[mydata] = b.newdata FROM a LEFT JOIN b ON a.bid=b.bid