有两个table,table1和table1,
table1的字段和数据如下:
partno scrap_ptr units unit_qty panel_qty
100231A0 20 U 20 0
100231A1 20 U 30 0
100231A2 20 U 40 0
200231A3 20 U 40 0table2的字段和数据如下:
partno scrap_ptr units wip_qty
100231A0 18 U 15
100231A0 20 U 25
100231A0 19 U 0
100231A1 20 U 30
100231A1 22 U 5
100231A2 20 U 40
要求一次能UPDATE table2,条件是两个表 partno , scrap_ptr,units 三个数据相同的则用wip_qty - unit_qty或panel_qty ,也就是执行后的table2的数据为:
partno scrap_ptr units wip_qty
100231A0 18 U 15
100231A0 20 U 5
100231A0 19 U 0
100231A1 20 U 0
100231A1 22 U 5
100231A2 20 U 0 返回table1中partno="200231A3"在table2中找不到匹配记录的partno值,非常感谢!在线等!
table1的字段和数据如下:
partno scrap_ptr units unit_qty panel_qty
100231A0 20 U 20 0
100231A1 20 U 30 0
100231A2 20 U 40 0
200231A3 20 U 40 0table2的字段和数据如下:
partno scrap_ptr units wip_qty
100231A0 18 U 15
100231A0 20 U 25
100231A0 19 U 0
100231A1 20 U 30
100231A1 22 U 5
100231A2 20 U 40
要求一次能UPDATE table2,条件是两个表 partno , scrap_ptr,units 三个数据相同的则用wip_qty - unit_qty或panel_qty ,也就是执行后的table2的数据为:
partno scrap_ptr units wip_qty
100231A0 18 U 15
100231A0 20 U 5
100231A0 19 U 0
100231A1 20 U 0
100231A1 22 U 5
100231A2 20 U 0 返回table1中partno="200231A3"在table2中找不到匹配记录的partno值,非常感谢!在线等!
update table2 set wip_qty = wip_qty - unit_qty
from table2 a inner join table1 b on a.partno = b.partno and
a.scrap_ptr = b.scrap_ptr and a.units = b.unitsselect partno from table1 a
where not exists(select 1 from table2 where partno = a.partno)
As
update table2 set wip_qty = wip_qty - unit_qty
from table2 a inner join table1 b on a.partno = b.partno and
a.scrap_ptr = b.scrap_ptr and a.units = b.unitsselect partno from table1 a
where not exists(select 1 from table2 where partno = a.partno)Go
Create Proc 存储过程名字
AS
SQL语句......
SQL语句......
SQL语句......
GO一般这样就行了,完整的是
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ] [ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]