http://dev.mysql.com/doc/mysql/en/delete.html
...
Multiple-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*] ...]
FROM table_references
[WHERE where_definition]
Or:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name[.*] [, tbl_name[.*] ...]
USING table_references
[WHERE where_definition]
...
From MySQL 4.0, you can specify multiple tables in the DELETE
statement to delete rows from one or more tables depending on a
particular condition in multiple tables. However, you cannot use ORDER
BY or LIMIT in a multiple-table DELETE.
The first multiple-table DELETE syntax is supported starting from MySQL
4.0.0. The second is supported starting from MySQL 4.0.2. The
table_references part lists the tables involved in the join. Its syntax
is described in Section 13.2.7.1, “JOIN Syntax”.
That's fo MyISAM and.. for InnoDB read Rhino's answer to Merlin's
thread (just above) "deleting rows in 2 tables" about Foreign key(s) and
on delete cascade
[EMAIL PROTECTED] wrote:
Hi,
I need to know the syntax I can use for a certain delete statement. This is
the idea:
- A table with users, which has at least these fields, a user id field and a
field containing its status (active, disabled, review).
- A table that links certain users to each other, containing at least these
fields: a link id, a user id 1 and a user id 2.
Now I want to delete all records from the second table when any of the
linked users is disabled, eg something like this:
DELETE FROM `matches` WHERE `userAid` IN ('SELECT `ID` FROM `users` WHERE
`status`='disabled')' OR `userBid` IN ('SELECT `ID` FROM `users` WHERE
`status`='disabled')'
The query must be run on a mysql server version 4.0.24
Thanks for any help with this one,
Mark
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
