Need help with a DELETE Syntax for Mysql

WizyWyg

I am trying to delete records from two different tables at one time.

In event table I have eventid and eventname
in participants table I have eventid and name

participants table associates who will be attending events in table 1. So eventid = eventid for how many people are attending.

What would be the delete syntax for Mysql, if I want to delete from table event eventid=1 and all those associated in the participants table where eventid=1

I know that these work:
DELETE FROM participants WHERE eventid=1
DELETE FROM event WHERE eventid=1

How can i combine the two?

running with MySQL 3.23.47

John_Canyon

See post below.

I stand corrected...

stolzyboy

well John Canyon, you are partially correct, this is straight from mysql.com

The first multi-table delete format is supported starting from MySQL 4.0.0. The second multi-table delete format is supported starting from MySQL 4.0.2.

The idea is that only matching rows from the tables listed before the FROM or before the USING clause are deleted. The effect is that you can delete rows from many tables at the same time and also have additional tables that are used for searching.

The .* after the table names is there just to be compatible with Access:

DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id
In the above case we delete matching rows just from tables t1 and t2.

If an ORDER BY clause is used (available from MySQL 4.0.0), the rows will be deleted in that order. This is really only useful in conjunction with LIMIT. For example:

DELETE FROM somelog
WHERE user = 'jcole'
ORDER BY timestamp
LIMIT 1
This will delete the oldest entry (by timestamp) where the row matches the WHERE clause.