delete with inner join????

[mgould00]

i am having trouble with the following statement:

delete from product inner join company_supplies on product.id = company_supplies.product_id where company_id = 1

it says i have an error near 'inner join'. can you not use inner join with delete in mysql? the select statement i use with the same syntax works fine...
thanks....

 

[QatQat]

Try use a transaction; send us the table scripts and more info about what you are trying to do.

Bye


Qatqat

The reason why my girlfriend can read my thoughts is because mine are properly written! (G.Lepore)

 

[sleipnir214]

But to more explicitly answer your question. Multi-table deletion is only supported in MySQL version 4.0.0. (

http://www.mysql.com/doc/en/DELETE.html)

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[mgould00]

well, to be more specific.....
i am not trying to delete from more than one table, but trying to delete several items from the products table by referencing a field in the customer_supplies table.

so i have the customer_id (in my example it is set to 1) i gather all the product_id in the customer_supplies table (could be 4,16,90,18,etc) where customer_id=1. then find all those product_id's in the products table and delete them.

hope that explains it better....
thanks

 

[sleipnir214]

If you are using MySQL version < 4.0.0, then you can only reference a single table name in a DELETE query. Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[swampBoogie]

If you have a version < 4 this means that you have to first do the select from the customer_supplier table, and then use the result to build a delete statement with explicit values.

 

[mgould00]

hmmmm....
i have version 3.23 or something like that. i guess i will take your advice swampboogie.
thanks fellas....