Multi-table Count/Update

shawjames

I'm kinda stumped on this one... so I thought I would ask for help.

Basically, I have 2 tables. One for products, and one for shopping carts. I want to total up the number of products sold in a particular month, and update a field that shows this total quantity sold.

gk_products is the first table, with pSold as the total number sold this month.

gk_carts is the other, that has pDate and pQty for when and how many an item was sold.

So my guess would be,

update gk_products, gk_carts set gk_products.pSold=sum(gk_carts.pQty) where gk_products.pNum=gk_carts.pNum and gk_carts.pDate like "2005-06-%"

But of course I get an "Invalid Group" error.

Anyone have some ideas on how I can do this?
Thanks!
-Shaw

leatherback

Hi,

Not sure, but I think you will have to do it in two steps: First select the sum for this month for each item, and them update them (?)

Lars_Berg

 update gk_products
 set pSold=(
select sum(pQty) 
from gk_carts
where gk_products.pNum=gk_carts.pNum and pDate between '2005-06-01'
and '2005-06-30')

shawjames

Hi Lars, thanks for the reply. I was excited to see your post because I did not know that this was possible. I cannot seem to get it to work though, this is my final code:

update ds_products, ds_carts, ds_orders set ds_products.pRank=(
select sum(ds_carts.pQty)
from ds_carts, ds_orders, ds_products
where ds_carts.onum=ds_orders.onum and ds_carts.cflag="Y" and ds_products.pNum=ds_carts.pNum and ds_orders.cDate between '2005-06-01'
and '2005-06-30'
)

I forgot I had to add a 3rd table to get the date. This is the error I get this error:
"#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select sum(ds_carts.pQty)
from ds_carts, ds_orders, ds_produc "

(Mysql Version 4.0.22-standard )

Any ideas what is wrong?

leatherback

Hey Lars,

Cool! Have to remember that!

Thx!

Lars_Berg

You are using an outdated version of Mysql. You need 4.1 for being able to do subqueries. If you are not in a position to upgrade you have to go the route with multiple queries.

leatherback

Ah, that explains a lot!!
(Hm.. do not feel like upgrading, but sounds like usefull about now..)

Thx,