[Resolved] UPDATE from a subquery

Nate

table: countries_total

site_id     country_id     hits
-------------------------------------
1          1               122
1          2               110
1          3               103

table raw_requests

site_id     country_id    hit (1=yes. 0=no)
--------------------------------
1          1              1
1          2              1
1          2              1
1          2              0
1          3              1

I need to UPDATE countries_total with raw_requests.

So after the UPDATE query runs table countries_total would look like:

site_id     country_id     hits
-------------------------------------
1          1               123 (added 1)
1          2               112 (added 2)
1          3               104 (added 1)

I know you can do INSERT... SELECT but you can't do UPDATE... SELECT.

Can this be done using a sub query or something?

All I could come up with is:

UPDATE countries_total,
raw_requests SET countries_total.hits = countries_total.hits + SUM( raw_requests.hit ) WHERE countries_total.country_id = raw_requests.country_id

MySQL complains saying invalid use of group by function (SUM)

LordShryku

You can use a join, provided you have the right version. I suppose a subquery would work too. Maybe you should show us what you tried that didn't work....

http://www.mysql.com/documentation/mysql/bychapter/manual_SQL_Syntax.html#UPDATE

Nate

I tried:

UPDATE countries_total,
raw_requests SET countries_total.hits = countries_total.hits + SUM( raw_requests.hit ) WHERE countries_total.country_id = raw_requests.country_id

MySQL says invalid use of group by function (i assume it is complaining about my use of SUM())

LordShryku

Yeah, sum is a group function. Here's a stab at the subquery way of doing it. MySQL sucks with subqueries though, so no telling if this will actually work....

UPDATE countries_total
   SET hits = countries_total.hits + (SELECT sum(hit) 
                                        FROM raw_requests 
                                       WHERE country_id = countries_total.country_id)

Nate

Thanks, let me try that.

Nate

works perfectly. thanks again. really appreciate the help

Nate

This almost works. There is one problem though (it seems so odd). If the table countries_total has a country_id that doesnt exist in the raw_hits table, instead of simply adding 0 to the row in countries_total, it makes the hits value 0. So say the original hits value was 2, instead of 2+0, it makes the value 0. Any idea why?

Originally posted by LordShryku
Yeah, sum is a group function. Here's a stab at the subquery way of doing it. MySQL sucks with subqueries though, so no telling if this will actually work....
UPDATE countries_total
   SET hits = countries_total.hits + (SELECT sum(hit) 
                                        FROM raw_requests 
                                       WHERE country_id = countries_total.country_id)
[/B]

Nate

Well i see why its making them 0 but not sure how to fix it yet. If SUM() returns 0 rows it returns NULL. If you do UPDATE something SET test = test + NULL you get 0 instead of test

LordShryku

There's a function called ifnull() that should take care of this...

UPDATE countries_total
   SET hits = hits + ifnull((SELECT sum(hit) 
                               FROM raw_requests 
                              WHERE country_id = countries_total.country_id),0)

Nate

thanks that fixed it