CREATE TABLE T1 ( id int, receipt int )CREATE TABLE T2 ( id int, qty int )INSERT INTO T1 (id, receipt) VALUES (1, 0) INSERT INTO T1 (id, receipt) VALUES (2, 0) INSERT INTO T1 (id, receipt) VALUES (3, 0) INSERT INTO T2 (id, qty) VALUES (1,2) INSERT INTO T2 (id, qty) VALUES (1,5) INSERT INTO T2 (id, qty) VALUES (1,8) INSERT INTO T2 (id, qty) VALUES (2,3) INSERT INTO T2 (id, qty) VALUES (2,3) INSERT INTO T2 (id, qty) VALUES (3,8) INSERT INTO T2 (id, qty) VALUES (3,7) INSERT INTO T2 (id, qty) VALUES (3,5)UPDATE T1 SET T1.receipt=0UPDATE T1 SET receipt=(SELECT SUM(qty) FROM T2 WHERE T1.id=T2.id)SELECT * FROM T1
create table test1(field1 char(5),field2 char(5),receipt number(8)); aaaaa,bbbbb,0 create table test2(field1 char(5),field2 char(5),qty number(3)); aaaaa,bbbbb,10 aaaaa,bbbbb,20 aaaaa,bbbbb,30 update test1 set test1.receipt=( select sum(qty) from test2 where test2.field1 =test1.field1 and test2.field2 =test1.field2 ) result-->test1:aaaaa,bbbbb,60 ok!
SQL Server: update 表1 set RECEIPT=a.sum_value from (select field1,field2,sum(qty) as sum_value from 表2 group by field1,field2) a where 表1.field1=a.field1 and 表2.field2=a.field2
Oracle: update 表1 set RECEIPT= (select a.sum_value from (select field1,field2,sum(qty) as sum_value from 表2 group by field1,field2) a where 表1.field1=a.field1 and 表2.field2=a.field2)
update 表1 set RECEIPT=a.sum_value from (select field1,field2,sum(qty) as sum_value from 表2 group by field1,field2) a where 表1.field1=a.field1 and 表2.field2=a.field2
不行啊,提示错误:"丢失操作符",我的代码为: cn.execute "update po set receipt=a.qty from (select t_po,t_item ,sum(qty) from receipt group by t_po,t_item) a where po.po=a.t_po and po.item=a.t_item" 请大家继续关注,谢谢!
(
id int,
receipt int
)CREATE TABLE T2
(
id int,
qty int
)INSERT INTO T1 (id, receipt)
VALUES (1, 0)
INSERT INTO T1 (id, receipt)
VALUES (2, 0)
INSERT INTO T1 (id, receipt)
VALUES (3, 0)
INSERT INTO T2 (id, qty)
VALUES (1,2)
INSERT INTO T2 (id, qty)
VALUES (1,5)
INSERT INTO T2 (id, qty)
VALUES (1,8)
INSERT INTO T2 (id, qty)
VALUES (2,3)
INSERT INTO T2 (id, qty)
VALUES (2,3)
INSERT INTO T2 (id, qty)
VALUES (3,8)
INSERT INTO T2 (id, qty)
VALUES (3,7)
INSERT INTO T2 (id, qty)
VALUES (3,5)UPDATE T1 SET T1.receipt=0UPDATE T1 SET receipt=(SELECT SUM(qty) FROM T2 WHERE T1.id=T2.id)SELECT * FROM T1
aaaaa,bbbbb,0
create table test2(field1 char(5),field2 char(5),qty number(3));
aaaaa,bbbbb,10
aaaaa,bbbbb,20
aaaaa,bbbbb,30
update test1
set test1.receipt=(
select sum(qty) from test2
where test2.field1 =test1.field1 and test2.field2 =test1.field2 )
result-->test1:aaaaa,bbbbb,60
ok!
update 表1 set RECEIPT=a.sum_value from (select field1,field2,sum(qty) as sum_value from 表2 group by field1,field2) a
where 表1.field1=a.field1 and 表2.field2=a.field2
update 表1 set RECEIPT=
(select a.sum_value from (select field1,field2,sum(qty) as sum_value from 表2 group by field1,field2) a
where 表1.field1=a.field1 and 表2.field2=a.field2)
where 表1.field1=a.field1 and 表2.field2=a.field2
cn.execute "update po set receipt=a.qty from (select t_po,t_item ,sum(qty) from receipt group by t_po,t_item) a where po.po=a.t_po and po.item=a.t_item"
请大家继续关注,谢谢!