Delete from multiple tables

[u3003-04-23]

I have 2 tables (myISAM format), which as far as I can tell are linked in the following way:

[cat]

catID

catTitle

[news]

newsTitle

catID

So say I have a cat Title called 'Cheese' and it has a catID of 20

I then add in a news article, which is about cheese, so I add it to the cheese category, and thus add in 20 to its catID field.

I then want to delete my category 'cheese' so I can do:

delete from `data`.`cat` where catID=20;

which is fine, but it leaves my record in the news database. I wanted to have some kind of cascade delete running as you get in Access. I have read the mySQL manual and it says you can do a multiple delete, so I tried:

delete from `data`.`cat`,`data`.`news` where catID=20

but I get this error:

Error Code : 1064

You have an error in your SQL syntax near 'delete from `data`.`cat`,`data`.`news` where catID=20' at line 3

(0 ms taken)

What am I doing wrong?

Thanks

Jim

[Ritesh]

Hello

This is taken from the MySQL Docs:

Quote:

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.

Which version of MySQL are you using?

[u3003-04-23]

Hi Ritesh – thanks for the reply. I think I'm not using v 4.0 – I'll download v4.0 and see if that works.

Thanks

Jim

[Author]

Posts