MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Roy Walker Date:July 11 2003 7:52pm
Subject:RE: Complex select statement
View as plain text  
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:egor.egorov@stripped] 
Sent: Friday, July 11, 2003 11:52 AM
To: mysql@stripped
Subject: Re: Complex select statement

"Roy Walker" <rwalker@stripped> 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 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?

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
 / /|_/ / // /\ \/ /_/ / /__   Egor.Egorov@stripped
/_/  /_/\_, /___/\___\_\___/   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/mysql?unsub=1

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