MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Nils Valentin Date:July 12 2003 1:44am
Subject:Re: Complex select statement
View as plain text  
2003年 7月 12日 土曜日 01:30、Roy Walker さんは書きました:
> 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?

YUP....

SELECT .... SELECT...

is a subselect.

Best regards

Nils Valentin
Tokyo/Japan

>
> 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

-- 
---
Valentin Nils
Internet Technology

 E-Mail: nils@stripped
 URL: http://www.knowd.co.jp
 Personal URL: http://www.knowd.co.jp/staff/nils

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