September 5, 2005 at 7:26 pm
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...
September 5, 2005 at 7:58 pm
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
September 5, 2005 at 8:14 pm
That works!!!
Thank you so much!!!
I sure gotta learn "GROUP BY" and all well.... thanks so much~!
September 5, 2005 at 8:27 pm
I am sure you can do better than me.
September 6, 2005 at 2:48 am
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
September 6, 2005 at 5:52 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy