MySQL Delete with JOINS

I have 4 joined tables:
tbl_users (primary key user_id)
tbl_user_subject_registration (foreign key user_id)
tbl_user_results (primary key result_id, foreign key user_id)
tbl_user_question_register (foreign key result_id)

I would like to delete all records in all tables related to a user_id supplied. Ideally this should be within one Delete statement. I've tried various things but always get a syntax error. Can anyone point out where I'm going wrong. Latest attempt below.

Code:

---

DELETE          tbl_users.*, tbl_user_subject_registration.*, tbl_user_results.*, tbl_user_question_register.*
FROM                tbl_users
INNER JOIN        tbl_user_subject_registration
ON                tbl_users.user_id = tbl_user_subject_registration.user_id
INNER JOIN        tbl_user_results
ON                tbl_users.User_id = tbl_user_results.user_id
INNER JOIN        tbl_user_question_register
ON                tbl_user_results.result_id = tbl_user_question_register.result_id
WHERE                tbl_users.user_id = ?

---

Cheers

DJ

What DB are you using. Most will let you cascade deletes on a join by join basis. If so set the relevant relationship to cascade on delet and you'll only need to delete the tblUsers record, the rest will be deleted automatically.

I know I've had problems getting deletes and updates to work on joined datasets with various databases. I'm actually not sure it's even valid sql (though I could be wrong)

It's a MySQL database.

Article I was following: http://www.electrictoolbox.com/artic...-table-delete/

DJ

I've *never* had a DBMS of any kind allow a delete via joins....

-after looking at the article-
Wow... interesting.

-- And what kind of syntax error do you get?

Tg

Why wouldn't you just "cascade" delete?

Well I can't see any difference between what you're doing and what's in that article. As TG asked - what's the error?

I found this link that shows you how to enforce referential indegrity in mySQL that might help. Page 2 shows how to cascade deletes:-

The exact error is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-3.23.55-nt]You have an error in your SQL syntax near 'tbl_users.*, tbl_user_subject_registration.*, tbl_user_results.*, tbl_user_quest' at line 1

/admin/users.asp, line 25
- Useful NOT!

I've inherited the database so there are no relationships enforced and ideally I'd rather leave well alone unless absolutely necessary.

DJ

I'm beginning to think it's version problem - the database is MySQL 3.23.55.

Delete with joins perhaps not supported this far back?

DJ

Taken from the article you originally linked to:-

Quote:

---

Using a join to delete records in MySQL is only possible with version 4.0 or higher.

---

I don't think your going to be able to do it on your version I'm afraid

Missed that cheers! I'll have a look at another method.

Thanks everyone for suggestions.

DJ

Quote:

---

Originally Posted by dee-u

Why wouldn't you just "cascade" delete?

---

Is this possible with the version of MySQL I use?

DJ

I've found the following: http://dev.mysql.com/doc/mysql/en/in...nstraints.html.

It would seem that although foreign key constraits are supported, ON DELETE CASCADE is not :(

Also I'd have to change all the table types to InnoDB.

I think I'm going to have to solve this in the script rather than being able to do it all in SQL - pity I'd rather the database did it.

Thanks to everyone.

DJ

That page says that on delete cascade is supported from MySQL 3.23.50 onwards but only if your using innoDB. If your using 3.23.55 you should be OK to use it.

I took a quick look at the innoDB stuff because I'd never heard of it and it looks like it's basically a layer that will add all the relational stuff for you. I know you're not keen to tinker too heavily with a db you've inherited but you might find this is a worthwhile exercise. You can alter the tables ratherthan recreating them and once you've set up the relationships properly then the db will do a lot of your work for you in future.

Oh yes - you are right - I must have misread yesterday.

How would I go about converting MyISAM tables to InnoDB?

DJ