MySQL Lists are EOL. Please join:

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