Deleting Rows from MySQL

JOIN works for SELECT, not DELETE

[timster]

Sorry for this purely SQL question, but the site that uses this SQL is written in PHP.

This SQL query (table names changed) find a set of records I need to delete from a MySQL 4.0 table (so subqueries are probably out).

SELECT tablename.id
FROM tablename JOIN cards 
ON tablename.card_id = relatedtablename.id
WHERE tablename.user_id =1 
AND relatedtablename.set_id =1

Any hints on how to delete these rows? (Short of upgrading to a newer MySQL, since it's not my server.) I'd prefer to do this with a SQL statement, instead of "punting" and writing a PHP page.

BTW, yes, everything is backed up carefully.

[coopster]

You can DELETE [dev.mysql.com] from multiple tables with MySQL >= 4.0.0

DELETE 
 tablename, 
 relatedtablename 
 FROM tablename 
 INNER JOIN relatedtablename 
 ON tablename.card_id = relatedtablename.id 
 WHERE tablename.user_id = 1 
 AND relatedtablename.set_id = 1 
;

Note:
This will delete matching rows from both tables. If you only want to delete from the first table, specify as such.

[timster]

Thanks coopster, that worked.

What timster learned today:
When using a join to identify records to be deleted from one MySQL table, use the syntax for deleting from multiple tables, and just specify the one table.

[coopster]

Are you saying you didn't catch my note at the end of the message, with the bold text and you had to do some restoring of data?

[timster]

No, no, coop' -- everything worked and the advice was spot on.

When I checked out the MySQL documentation before posting my question, I looked at "Deleting from multiple tables" and thought that wasn't what I wanted, since I was only deleting from one table.

Maybe it would have been more clear if I'd just posted the code:

DELETE 
 tablename
 FROM tablename 
 INNER JOIN relatedtablename 
 ON tablename.card_id = relatedtablename.id 
 WHERE tablename.user_id = 1 
 AND relatedtablename.set_id = 1 
;

[coopster]

Ah, gotcha. And there we have a nice example of how to specify multiple tables in the

DELETE

statement to delete rows from just one table. Nice work timster.