Why does this query fail!?

[Guern]

I'm probably missing somthing that is staring me in the face, but....

insert into stats_copy (domainname,d,d2) select domainname, datetime, sum(hits) as thits from stats where datetime >= DATE_SUB(NOW(),INTERVAL 7 DAY) and domainname like 'test%' and server=2 group by datetime ON DUPLICATE KEY UPDATE d
2=d2+thits

I get an error

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 'DUPLICATE KEY UPDATE d2=d2+thits' at line 1

The same query without the 'on duplicate key update' part that I used to initially populate the table works fine.

I'm running 4.1.7, and the on duplicate key update is supposed to have been supported since 4.1.

Anyone spot the why this fails?

Thanks.

 

[azzazzello]

try this in the duplicate part

... ON DUPLICATE KEY UPDATE d2=VALUES(d2)+thits

 

[r937]

i don't think that's going to work

i don't think this will either, but this is what i would try --

insert
into stats_copy
( domainname,d,d2 )
select domainname, datetime, sum(hits) as thits
from stats
where datetime >= DATE_SUB(NOW(),INTERVAL 7 DAY)
and domainname like 'test%'
and server=2
group by datetime
ON DUPLICATE KEY
UPDATE d2 = d2 +
( select sum(hits) as thits
from stats
where datetime >= DATE_SUB(NOW(),INTERVAL 7 DAY)
and domainname like 'test%'
and server=2 )

the problem is, at the point where the duplicate key is detected, you're working on a particular set of values as generated by the SELECT, but i doubt that the other values in that row (i.e. thits) are available in the synyax

hence the subquery

but even if it works, you may not like the solution, because you've only updated thits, and not the datetime

r937.com | rudy.ca

 

[Guern]

Hi. Thanks for the suggestions.

I only actually want to update thits. However, I don't think the issue is because thits is not available, becuase if I replace

ON DUPLICATE KEY UPDATE d2=d2+thits

ON DUPLICATE KEY UPDATE d2=d2+1

It still produces the same error.

There are plenty of examples in the mysql manual just like this. It's almost suggesting my end there is a syntax error of some sort.