* Zaxpaw
> I am getting a syntax error from MySQL when executing the following
> query (names substituted):
>
> Delete From Table1 WHERE Related_ID IN (SELECT Related_ID FROM Table2
> WHERE Another_ID='1');
>
> What is going wrong?
Version 3.x does not support sub-selects.
> My guess is that the SELECT is considered a subquery, but how else do
> you get the "selected rows" for the IN() argument?
You can do it in multiple steps. First, get the id's from the first SELECT
in the example from the manual, quoted below. You need a programming
language for this. Are you using a programming language with mysql?
Second, you put these id's in a string, separated by comma, so it would look
like this: $ids = "234,634,434,6235,32"
..then you construct the delete statement:
DELETE FROM related_table
WHERE related_column IN ($ids)
... and finally execute it after it is expanded to this:
DELETE FROM related_table
WHERE related_column IN (234,634,434,6235,32)
> Here is the relevant section from the manual:
>
> A.5.5 Deleting Rows from Related Tables
>
> As MySQL doesn't support subqueries (prior to Version 4.1), nor the use
> of more than one table in the DELETE statement (prior to Version 4.0),
> you should use the following approach to delete rows from 2 related
> tables:
>
> SELECT the rows based on some WHERE condition in the main table.
>
> DELETE the rows in the main table based on the same condition.
>
> DELETE FROM related_table WHERE related_column IN (selected_rows).
If you don't use a programming language, you can also do this using only the
standard mysql client, but using two instances...
SELECT CONCAT('DELETE FROM Table1 WHERE Related_ID = ', Table1.Related_ID,
';')
FROM Table1, Table2
WHERE Another_ID='1' AND Table1.Related_ID = Table2.Related_ID;
If you put this (or similar) query in a text file called "del_tab1.sql", you
can execute the deletion with a "double" mysql invocation similar to this:
mysql --skip-column-names dbname < del_tab1.sql | mysql dbname
You could try it without actually doing the delete by executing this:
mysql --skip-column-names dbname < del_tab1.sql
This sould list all delete statements to the screen only, without executing
them. You could redirect this output to a file, and then execute this file:
mysql --skip-column-names dbname < del_tab1.sql > do_delete.sql
mysql dbname < do_delete.sql
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
