You are not allowed to use grouping functions in the WHERE clause. To restrict on grouped
values, you have to put them into the HAVING clause.
Cheers
/rudy
-----Original Message-----
From: Roy Walker [mailto:rwalker@stripped]
Sent: dinsdag 15 juli 2003 23:25
To: Rudy Metzger; mysql@stripped
Subject: RE: Complex select statement
Still having a problem with this. Still have one last thing that isn't working. This is
MySQL 4.0.13.
UPDATE table_tmp,table2_daily SET table_tmp.period_count = table_tmp.count - table2.count
WHERE table_tmp.id = table2.id AND MAX(table2.timestamp);
This is giving me:
ERROR 1111: Invalid use of group function.
I am trying to update the period_count field for all the records in table_tmp, by setting
the equal to the count from table_tmp MINUS the count from table2 where the id's match and
it is the newest record in table2.
Any ideas?
Please don't tell me to how to do it 4.1! Please! :)
Roy
-----Original Message-----
From: Rudy Metzger [mailto:rudy.metzger@stripped]
Sent: Monday, July 14, 2003 3:49 AM
To: Roy Walker; mysql@stripped
Subject: RE: Complex select statement
I never heard before that you can use a select statement in an arithmetic expression. Only
ALL, ANY, MIN, MAX, =, (and some other which do not come to my mind quickly) should work.
Anyway, it is considered a "subselect" and therefore does not work yet. However, in 4.1
you should also be able to formulate it like this:
INSERT INTO table2( id, count, period_count, date )
SELECT t1.id, t1.count, t1.count - x.count, to_days(now()-1)
FROM table1 t1,
(SELECT count
FROM table2
WHERE id = t1.id
ORDER BY timestamp desc
LIMIT 1) x
This would also be the way how I would do it in a different DB (e.g. ORACLE).
Cheers
/rudy
-----Original Message-----
From: Roy Walker [mailto:rwalker@stripped]
Sent: vrijdag 11 juli 2003 18:30
To: mysql@stripped
Subject: RE: Complex select statement
Eva,
Thanx for your help. Still having an issue with this. I forgot to mention I am running
MySQL 4.0.13. Tried both of these statements:
INSERT INTO table2 (id,count,period_count,date) SELECT id as id1, count, (count - (SELECT
count FROM table2 WHERE id=id1 order by timestamp desc
limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;
INSERT INTO table2 (id,count,period_count,date) SELECT @id1:=id, count,
(count - (SELECT count FROM table2 WHERE id=@id1 order by timestamp desc
limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;
They both give me an error for the 'SELECT count FROM table2 WHERE id=id1' section. Is
this illegal syntax? Would this be considered a subselect?
Thanx,
Roy
-----Original Message-----
From: "Paracková Eva, Ing" [mailto:parackova@stripped]
Sent: Friday, July 11, 2003 12:30 AM
To: Roy Walker
Subject: RE: Complex select statement
try this:
INSERT INTO table2 (id,count,period_count,date) SELECT id as id1, count,
(count - (SELECT count FROM table2 WHERE id=id1 order by timestamp desc
limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1;
i am not sure, the subselect is ok. if the "id1" alias will not work, then
try to use a variable (... SELECT @id1:=id ... WHERE id=@id1 ...).
i just corrected a few syntax mistakes in your query.
eva
-----Original Message-----
From: Roy Walker [mailto:rwalker@stripped]
Sent: Friday, July 11, 2003 7:19 AM
To: mysql@stripped
Subject: Complex select statement
If anyone could tell me what I am doing wrong here, I would greatly
appreaciate it.
Have the following tables:
table1: id, count
table2: id, count, period_count, date, timestamp
Trying to do the following; get all rows from table 1 and insert them into
table2 while setting period_count to count.table1 minus the most recent
entry for that id in count.table2, then set the date field to the previous
day.
Here goes:
INSERT INTO table1 (id,count,period_count,date) SELECT id,count FROM table1,
(count.table1 - (SELECT count FROM table2 WHERE MAX(timestamp) AND
id.table2=id)) AS period_count, date='TO_DAYS(NOW() -1)';
Sorry if this is easy, but I could not find an example of any mathematic
functions while doing an INSERT ... SELECT.
Thanx,
Roy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=1

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.