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 [EMAIL PROTECTED] 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:[EMAIL PROTECTED] > 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 [EMAIL PROTECTED] ...). > i just corrected a few syntax mistakes in your query. > > eva > > -----Original Message----- > From: Roy Walker [mailto:[EMAIL PROTECTED] > Sent: Friday, July 11, 2003 7:19 AM > To: [EMAIL PROTECTED] > 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: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
