Regarding SubQueries in MySQL

[roopa]

Hi All,
Iam using MySQL ver 3.23 and i need to frame a query with DELETE syntax
Actually i have a table1 with 3 fields and table2 with many other fields including timefield
Now i need to delete all the records from table1 using the time from table2 ..how do i do this?
like DELETE from table1 where table2.time between '2003-09-29 16:00:00' and '2003-09-30 16:00:00' does not work
Error that comes is 1109 "Unknown table table2"
As 3.23 doesnot support subqueries how so i frame the query?
Thanks in advance
Roopa

[onyx]

Well from that statement it would be imposible to determine what the link is between table1 and table 2. (you would need some sort of join). I think you are going to have to peform a few different queries. Hence you would perform a select query:

select table1primary from table1, table2 where table1.something = table2.something and table2.time= blah blah

You should be able to use what is returned from this in order to delete the enteries.

[mattr]

Since MySQL 3 doesn't support subqueries you'll have to make two queries.

One to get the IDs and one to delete where ID in ($your_ID_list)