Re: MySQL Delete with JOINS
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)
Re: MySQL Delete with JOINS
Re: MySQL Delete with JOINS
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
Re: MySQL Delete with JOINS
Why wouldn't you just "cascade" delete?
Re: MySQL Delete with JOINS
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:-
Re: MySQL Delete with JOINS
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
Re: MySQL Delete with JOINS
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
Re: MySQL Delete with JOINS
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
Re: MySQL Delete with JOINS
Missed that cheers! I'll have a look at another method.
Thanks everyone for suggestions.
DJ
Re: MySQL Delete with JOINS
Quote:
Originally Posted by dee-u
Why wouldn't you just "cascade" delete?
Is this possible with the version of MySQL I use?
DJ
Re: MySQL Delete with JOINS
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
Re: MySQL Delete with JOINS *UNRESOLVED* Version limitations
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.
Re: MySQL Delete with JOINS *UNRESOLVED* Version limitations
Oh yes - you are right - I must have misread yesterday.
How would I go about converting MyISAM tables to InnoDB?
DJ