I am using 4.0.13, not 4.0.1. Good to know what was wrong with the previous statement. How bout this one?
DROP TABLE IF EXISTS table_tmp; CREATE TABLE table_tmp ( `id` varchar(12) NOT NULL default '0', `count` mediumint(15) NOT NULL default '0', `period_count` mediumint(15) NOT NULL default '0', `date` date NOT NULL default '0000-00-00', `timestamp` timestamp(14) NOT NULL, ) TYPE=MyISAM; INSERT INTO table_tmp (id,count,date) SELECT id, count, (TO_DAYS(NOW() -1)) FROM table1; UPDATE table_tmp SET period_count = (count - SELECT count FROM table2 WHERE id.table_tmp=id.table2 AND WHERE MAX(timestamp.table2)); INSERT INTO table2 (id,count,period_count,date,timestamp) SELECT id,count,period_count,date,timestamp FROM table_tmp; DROP TABLE table_tmp; I get an error on the UPDATE command. Any ideas? Thanx, Roy -----Original Message----- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Friday, July 11, 2003 11:52 AM To: [EMAIL PROTECTED] Subject: Re: Complex select statement "Roy Walker" <[EMAIL PROTECTED]> wrote: > > 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? 1. 4.0.1 is too old and unstable version. 2. You can't insert data into the table, which you use in the SELECT part too (table2). Retrieve data from table2 to the temporary table and then insert data to the table2 using table1 and temporary table. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
