I never heard before that you can use a select statement in an arithmetic expression.
Only ALL, ANY, MIN, MAX, =, (and some other which do not come to my mind quickly)
should work.
Anyway, it is considered a "subselect" and therefore does not work yet. However, in
4.1 you should also be able to formulate it like this:
INSERT INTO table2( id, count, period_count, date )
SELECT t1.id, t1.count, t1.count - x.count, to_days(now()-1)
FROM table1 t1,
(SELECT count
FROM table2
WHERE id = t1.id
ORDER BY timestamp desc
LIMIT 1) x
This would also be the way how I would do it in a different DB (e.g. ORACLE).
Cheers
/rudy
-----Original Message-----
From: Roy Walker [mailto:[EMAIL PROTECTED]
Sent: vrijdag 11 juli 2003 18:30
To: [EMAIL PROTECTED]
Subject: RE: Complex select statement
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
