MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Roy Walker Date:July 15 2003 9:24pm
Subject:RE: Complex select statement
View as plain text  
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

Thread
Complex select statementRoy Walker11 Jul
RE: Complex select statementRoy Walker11 Jul
  • Re: Complex select statementEgor Egorov11 Jul
  • Re: Complex select statementNils Valentin12 Jul
RE: Complex select statementRoy Walker11 Jul
RE: Complex select statementRudy Metzger14 Jul
RE: Complex select statementRoy Walker15 Jul
  • Re: Complex select statementMichael Iatauro15 Jul
RE: Complex select statementRoy Walker16 Jul
RE: Complex select statementRudy Metzger16 Jul