日期:2014-05-18  浏览次数:20556 次

关于数据库的问题。
两个表。结构如下
表MIS_InsertInfo
  InsertInfo_Name Number InsertInfo_Amount  
  a 1 10
  b 2 50
表MIS_Stocks
  Stocks_Name Stocks_Total
  a 20
  b 30

表MIS_InsertInfo是入库表,MIS_Stocks是库存表,我只想把MIS_Stocks a 的数量变为30 b的变为80 SQL语句怎么写!

------解决方案--------------------
SQL code

--> 测试数据:[MIS_InsertInfo]
if object_id('[MIS_InsertInfo]') is not null drop table [MIS_InsertInfo]
create table [MIS_InsertInfo]([InsertInfo_Name] varchar(1),[Number] int,[InsertInfo_Amount] int)
insert [MIS_InsertInfo]
select 'a',1,10 union all
select 'b',2,50
--> 测试数据:[MIS_Stocks]
if object_id('[MIS_Stocks]') is not null drop table [MIS_Stocks]
create table [MIS_Stocks]([Stocks_Name] varchar(1),[Stocks_Total] int)
insert [MIS_Stocks]
select 'a',20 union all
select 'b',30

update [MIS_Stocks] set [Stocks_Total]=[Stocks_Total]+a.[InsertInfo_Amount]
from [MIS_InsertInfo] a where a.InsertInfo_Name=[MIS_Stocks].Stocks_Name

select * from [MIS_Stocks]

/*
Stocks_Name    Stocks_Total
a    30
b    80
*/