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? 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
