MySQL Delete with JOINS

[dj4uk]

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.

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

[FunkyDexter]

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)

[dj4uk]

It's a MySQL database.

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

DJ

[techgnome]

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

[dee-u]

Why wouldn't you just "cascade" delete?

[FunkyDexter]

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:-

[dj4uk]

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

[dj4uk]

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

[FunkyDexter]

Taken from the article you originally linked to:-

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

[dj4uk]

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

Thanks everyone for suggestions.

DJ

[dj4uk]

Why wouldn't you just "cascade" delete?

Is this possible with the version of MySQL I use?

DJ

[dj4uk]

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

[FunkyDexter]

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.

[dj4uk]

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

How would I go about converting MyISAM tables to InnoDB?

DJ