现在有两个表结构如下
表1:
Stock qty1
1 5
2 4
3 2
........
表2
Stock qty2
1 1
1 4
1 2
2 1
2 5
3 2
3 1
........
问题是我要更新表1:qty1的数量,其数量等于表2:sum(qty2)的和
怎么在Update里面用游标来更新表1里面所有qty1的数量
请教
表1:
Stock qty1
1 5
2 4
3 2
........
表2
Stock qty2
1 1
1 4
1 2
2 1
2 5
3 2
3 1
........
问题是我要更新表1:qty1的数量,其数量等于表2:sum(qty2)的和
怎么在Update里面用游标来更新表1里面所有qty1的数量
请教
qty1 = B.qty2
FROM 表1 A, (
SELECT Stock, qty2 = SUM(qty2)
FROM 表2
GROUP BY Stock
)B
WHERE A.Stock = B.Stock
create table 表2 (a int,b int)
go
insert into 表1
select 1,5
union all
select 2,4
union all
select 3,2
insert into 表2
select 1,1 union all
select 1,4 union all
select 1,2 union all
select 2,1 union all
select 2,5 union all
select 3,2 union all
select 3,1 UPDATE A SET
b = B.b
FROM 表1 A, (
SELECT a, b = SUM(b)
FROM 表2
GROUP BY a
)B
WHERE A.a = B.aselect * from 表1
drop table 表1,表2-------------------------------
zjcxc(邹建) 写的正确。~~
set后面直接可以写qty1 = sum(B.qty2)+sum(B.qty3)吗
(Stock INT,
quty1 int
)CREATE TABLE TABLESTOCK1
(Stock INT,
quty2 int ,
quty3 int
)
insert into TABLESTOCK
select
1 , 5 union all select
2 , 4 union all select
3 , 2insert into TABLESTOCK1
select
1 , 1 , 1 union all select
1 , 4 , 1 union all select
1 , 2 ,1 union all select
2 , 1 ,1 union all select
2 , 5 ,1 union all select
3 , 2 ,1 union all select
3 , 1 ,1
UPDATE A SET
quty1 = B.quty
FROM TABLESTOCK A, (
SELECT Stock, quty = SUM(quty2+quty3)
FROM TABLESTOCK1
GROUP BY Stock
)B
WHERE A.Stock = B.Stock
--------------------------------
1 10
2 8
3 5