Delete multiple rows in MySQL?

Yari

Hi,
I am trying to delete several rows from two or even three tables in mysql.

I would like to do this in one select statement, and after reading the MySQL manual this is what I came up with:

delete salon_city, salon from salon_city, salon where salon_city.city_id='$my_id' and salon_city.city_id=salon.city_id

After extensive testing, I still can't get it to work, even though $my_id is correct, and corresponds to a feild in the database.

The error I get is:

error doing select 1064:You have an error in your SQL syntax near 'salon_city, salon from salon_city, salon where salon_city.city_id='10' and salon' at line 1

So there must be something wrong in my statement.. but I just can't figure it out.

Any help would be greatly appreciated!

Cheers.

LordShryku

Using delete will delete whole rows. If that is your intention, you need to start you statement with DELETE FROM....

DELETE FROM salon_city, salon 
WHERE salon_city.city_id='$my_id' 
AND salon_city.city_id=salon.city_id

Yari

Thanks... and yes, I do want to delete rows.

I still get the error message:

error doing select 1064:You have an error in your SQL syntax near ' salon WHERE salon_city.city_id='10' AND salon_city.city_id=salon.city_id' at line 1

My new select statement looks like this:

DELETE FROM salon_city, salon WHERE salon_city.city_id='$my_id' AND salon_city.city_id=salon.city_id

Thanks for you help so far.

LordShryku

Okie.....try this....

DELETE FROM salon_city, salon 
USING salon_city, salon
WHERE salon_city.city_id=salon.city_id 
AND salon_city.city_id='$my_id'

Yari

Argghh.... still the error:

error doing select 1064:You have an error in your SQL syntax near ' salon USING salon_city, salon WHERE salon_city.city_id=salon.cit' at line 1

Now... it must be something in the middle of the statement... 'cos that's where the error message comes up.

I have checked, and double checked the names of my tables, and the ID feilds that I am calling... but they are all correct.

This has really got me stumped.

I copied and pasted the code directly from your last post, so it's all there, and I won't bother repeating it.

Thanks,

LordShryku

May also be your MySQL version that doesn't support this....

Yari

Ah, that could be it, as its a fairly old version I think (MySQL 3.23.32).

I guess I will have to do a work-around for now...

Thanks for you patience.