My Delete query does not work, need advice.

[Anonymous]

ive got the following sql for my 3.23.51 MySQL database using PHP scripting, ive tried several combinations below and none work:

DELETE cartcontents.*, sid.sid_sid
FROM sid RIGHT JOIN cartcontents ON sid.sid_sid = cartcontents.carsid
WHERE sid.sid_sid Is Null

DELETE FROM cartcontents.*
sid RIGHT JOIN cartcontents ON sid.sid_sid = cartcontents.carsid
WHERE sid.sid_sid Is Null


DELETE FROM cartcontents
RIGHT JOIN cartcontents ON sid.sid_sid = cartcontents.carsid
WHERE sid.sid_sid Is Null

I want it to delete records in the table named 'cartcontents' where another record does not exist in the 'sid' table and it doesnt work.
If i replace the 'DELETE' in the above sql with 'SELECT', it will display all the correct records that i wish to delete fine without errors, but how do i turn the select query into a delete query? simply replacing the 'SELECT' with 'DELETE' doesnt work as you can see above, it gives me errors.

The error i get is: 1064-You have an error in your SQL syntax near 'cartcontents.*, sid.sid_sid FROM sid RIGHT JOIN cartcontents ON sid.sid_sid = ca' at line 1

Ive been trying for an hour now to make it work but get the same error, thing is, it will work as a select query no worries, ive eventried it in an access database and the delete query works fine.

Can anyone tell me what im doing wrong? Is it that mysql does not support this 2 table delete query?

 

[Anonymous]

The multi table delete format is supported starting from MySQL 4.0.0.

The idea is that only matching rows from the tables listed BEFORE the FROM clause is 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

In the above case we delete matching rows just from tables t1 and t2.

ORDER BY and using multiple tables in the DELETE is supported in MySQL 4.0.