Qty subtraction - multiple rows
-
Is this possible to do using only the SQL Code?
I have Invoice_Products table... ( for example )
ProdName ----- QtyOrdered
Car 1
Boat 2
Car 3
Bike 1
( Notice there's "Car" in two separate rows.. )
And I need to deduct these from the Products table.
When I run this code:
UPDATE Products
SET Products.qty = Products.qty - InvoiceProducts.qtyOrdered
FROM InvoiceProducts
WHERE Products.prodCode = InvoiceProducts.prodCode
It only takes into account ONE row ( second one) of "Car", instead of both... so it will deduct 3 in this case.
Is there some SQL code that I could use to make this work?
Thanks...
-
Hi friend,
try this:
create table products(
product_id varchar(25),
qty int
)
create table invoice_product(
product_id varchar(25),
qty int
)
insert into products
select 'car', 10
insert into products
select 'boat', 10
insert into products
select 'bike', 10
insert into invoice_product
select 'car', 1
insert into invoice_product
select 'boat', 2
insert into invoice_product
select 'car', 3
insert into invoice_product
select 'bike', 1
go
select * from products
select * from invoice_product
go
update products
set qty = a.qty - b.qty
from products a, (select product_id, sum(qty) qty from invoice_product group by product_id) b
where a.product_id = b.product_id
Leo
-
That works!!!
Thank you so much!!!
I sure gotta learn "GROUP BY" and all well.... thanks so much~!
-
I am sure you can do better than me.
-
Maybe you will understand this syntax easier: You do not need grouping because of the sub-select.
update products
set qty = a.qty - (select sum(b.qty) from invoice_product b where b.product_id = a.product_id)
from products a
5ilverFox
Consulting DBA / Developer
South Africa -
Or using ANSI-92 SQL, rather than the archaic ANSQL-89 (older Sybase, SQL 6.5) join-in-the-where-clause
update products
set qty = a.qty - b.qty
from products a JOIN (select product_id, sum(qty) qty from invoice_product group by product_id) b ON a.product_id = b.product_id
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply