sql server中联合两个表update数据

最新推荐文章于 2025-05-21 08:34:48 发布

原创最新推荐文章于 2025-05-21 08:34:48 发布 · 7.4k 阅读

0 ·

CC 4.0 BY-SA版权

sql server 专栏收录该内容

5 篇文章

订阅专栏

该博客介绍了如何在SQL Server中联合两个表进行数据更新。首先创建了一个新表temp2，将原表T_wStock的数据复制过去。然后通过JOIN操作找出T_wStock中与temp1不匹配的记录，根据条件更新T_wStock表的GoodsStyle字段。最后展示了一些查询语句来检查更新后的数据和原始数据的对应情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

INSERT INTO temp2 --新建一个表，表结构跟原表结构一样，并表原表中的数据插入进去，再做测试
SELECT WarehouseTaskCode, CustCode, GoodsCode, GoodsName, GoodsModel, GoodsPackNum, GoodsPieceNum, GoodsCubage, GoodsWeight, GoodsGWeight, WarehousePosition, GoodsLock, InStockDate, GoodsPrice, GoodsTotalPrice, DamageTag, InStockType, ExpectPieceNum, ExpectCubage, ExpectWeight, ExpectGWeight, NeatenID, Recorder, RecordDate, Modifier, ModifyDate, OriginalCountry, BarcodeNum, BarcodePrefix, GoodsLatchCode, GoodsProductiondate, GateWayCode, GateWayDate, GoodsStyle, BelongToWarehouse, FinanceYear, FinanceMonth, GoodsPackUnit, GoodsPieceUnit, BargainValuta, EradioCode, CIQGoodsFNum, CIQGoodsSNum, XmlCol, Reserve_One, Reserve_Two, Reserve_Three, ZeroDate, GoodsType, DamageTagLevel FROM T_wStock

select COUNT(1) FROM T_wStock
select COUNT(1) FROM temp2

select COUNT(1) FROM temp2

select GoodsCode,GoodsStyle ,htype from 
temp2 inner join temp1 on temp2.GoodsCode = dbo.temp1.num
where temp2.CustCode='GD01' and dbo.temp2.GoodsStyle!=dbo.temp1.htype
GROUP BY GoodsCode,GoodsStyle ,htype

update T_wStock set T_wStock.GoodsStyle = temp1.htype from
T_wStock,temp1 where T_wStock.GoodsCode = temp1.num and T_wStock.CustCode='GD01'

SELECT * FROM dbo.T_wStock

select * from T_wStock where CustCode='GD01' and GoodsCode in (
select num from temp1)

select * from temp1

select GoodsCode,GoodsStyle ,htype from 
T_wStock inner join temp1 on T_wStock.GoodsCode = dbo.temp1.num
where T_wStock.CustCode='GD01' and dbo.T_wStock.GoodsStyle!=dbo.temp1.htype
GROUP BY GoodsCode,GoodsStyle ,htype

SELECT GoodsCode,COUNT(1) 
FROM dbo.T_wStock
WHERE GoodsCode IN(SELECT num FROM dbo.temp1)
GROUP BY GoodsCode

select num,COUNT(1) from temp1 group by num