请教一个UPDATE语句的写法

[bluery]

假如有两张表:
1.TBL_TODAYSTOCK(库存表)
字段:VENDER(供应商),STOCK(库存)
2.TBL_INSTOCK(进货表)
字段:VENDER(供应商),DATE(进货日期),WEIGHT(进货量)

现在需要做一件事情:
统计当天每个供应商的进货总量,将其更新至对应的库存表的库存字段中

谢谢

[YY_MM_DD]

update TBL_TODAYSTOCK aa,
(select VENDER,DATE,count(WEIGHT) from TBL_INSTOCK group by VENDER,DATE) tt
set aa.STOCK=tt.STOCK
where aa.VENDER=tt.VENDER

[YY_MM_DD]

update TBL_TODAYSTOCK aa,
(select VENDER,DATE,count(WEIGHT) toatl from TBL_INSTOCK group by VENDER,DATE) tt
set aa.STOCK=tt.total
where aa.VENDER=tt.VENDER

[wh62592855]

不知道楼主的意思是用每天的进货量替代原来的库存还是加入到库存中
如果是后者
则改为set aa.stock=aa.stock+tt.total

[DongloveRen]

[Quote=引用 1 楼 YY_MM_DD 的回复:]
SQL code
update TBL_TODAYSTOCK aa,
(select VENDER,DATE,count(WEIGHT) from TBL_INSTOCK group by VENDER,DATE) tt
set aa.STOCK=tt.STOCK
where aa.VENDER=tt.VENDER
[/Quote]
学习！

[cosio]

你这种可以用触发器来解决!

[gisinfo]

update语句都不会

你还叫他用触发器

[wangsong145]

[Quote=引用楼主 bluery 的帖子:]
假如有两张表:
1.TBL_TODAYSTOCK(库存表)
字段:VENDER(供应商),STOCK(库存)
2.TBL_INSTOCK(进货表)
字段:VENDER(供应商),DATE(进货日期),WEIGHT(进货量)

现在需要做一件事情:
统计当天每个供应商的进货总量,将其更新至对应的库存表的库存字段中

谢谢
[/Quote]
UPDATE TBL_TODAYSTOCK SET STOCK=(SELECT SUM(WEIGHT) GROUP BY VENDER WHERE DATE=SYSDATE)

[vc555]

可考虑用merge into .
因为可能某新供货商在库存表中不存在。

[KingNE]

呵呵

[chengtwn]

用触发器比较好，高效安全

[hawkliu333]

支持,这个效率也非常高

[hawkliu333]

支持8楼,merge into效率也非常高,但要有一个中间表,merge into 需要表结构相同

[buffalodong326]

MERGE INTO TBL_TODAYSTOCK T1
USING (SELECT VENDER,WEIGHT FROM TBL_INSTOCK WHERE DATE=sysdate) T2
ON ( T1.VENDER=T2.VENDER)
WHEN MATCHED THEN
UPDATE SET T1.STOCK = T2.WEIGHT
WHEN NOT MATCHED THEN
INSERT (VENDER,STOCK)(SELECT VENDER,WEIGHT FROM TBL_INSTOCK WHERE DATE=sysdate) ;

是这样写吧印象中