[RESOLVED] Deleting across related tables

David_James

Hello. I just succeeded in deleting everything in two of my tables using the following statement:

delete user, job from user, job where user.user in (54) or job.user in (54);

user.user is the primary (auto_increment) key for the user table and job.user is the foreign key in the job table. Essentially, if I delete a user, then I want all his jobs deleted.

devinemke

DELETE user.*, job.* FROM user, job WHERE user.user = '54' AND job.user = '54'

David_James

Thanks.

The reason why I used in() is for deleting multiple customers/jobs. So I will adapt your code to this:

delete user.*, job.* from user job where user.user in (2,45) AND job.user in (2,45);

Will this work as expected?? (that is, delete all users and all jobs where user in (2,45))

I want you to verify this first before I go and screw up my table data again!!

Lars_Berg

Define the foreign key with on delete cascade instead. Then you just need to delete from the user table.

David_James

Lars Berg wrote:
Define the foreign key with on delete cascade instead. Then you just need to delete from the user table.

Can you do that with MySQL 4.0 and if so, how? ... actually I just checked the manual and it said that it only works for InnoDB tables and not the standard MyISAM (which I am using). Darn! Lars, if in fact InnoDB is the only way, as it is a better engine for relational database schema, then would you say that I could use InnoDB just as well as MyISAM (that is, it is fairly easy to learn and has the same features)??

Drakla

I'd prefer to see that query as

delete user, job from user, job where user.user in (2,45) AND job.user = user.user;

Even though in this case they'll do the same thing, a match of user = 2 and job.user = 45 will cause a delete, which isn't exactly right, if you know what I mean, and in a similar query could cause bad batch deleting.

David_James

Drakla wrote:
I'd prefer to see that query as

delete user, job from user, job where user.user in (2,45) AND job.user = user.user;

Even though in this case they'll do the same thing, a match of user = 2 and job.user = 45 will cause a delete, which isn't exactly right, if you know what I mean, and in a similar query could cause bad batch deleting.

I tried your sql and it worked as expected. 2 users each with 2 jobs. 6 rows affected (thats 2 in the user table and 4 in the job table).

OK, now to go a little deeper, what if I had a third table (you can reference my other thread if you want) that is related to the previous two, essentially:

forms table:
primary key: forms
foreign key: job
foreign key: user

AND I want to delete all the way down (cascade)

How would you change your sql?

Drakla

Okey dokey, I'll try and make the query really strict again, truth is you can get away with murder using a joined delete and programming it 'badly' (badly in a teacher tutting away at you) can be a helluva more efficient - but that's not for here, as Marjorie Daws would say

DELETE user, job, forms 
FROM user, job, forms
  WHERE 
    user.user in (2,45) AND 
    job.user = user.user AND
    forms.user = user.user AND
    forms.job = job.job;

David_James

drakla, your awsome!!

Did some testing and it seems to work reliably each time.

Please verify the following:

To delete only the jobs for particular users, simply remove "user" from the first part of the delete statement, for instance:

delete job, forms   <--- right here
	from user, job, forms 
		where 	user.user in (13,14) and 
			user.user = job.user and 
			user.user = forms.user and 
			job.job = forms.job;

If I had yet another level down in the table hiearchy called "parts" (schema: user->job->forms->parts, therefore parts having all the ascendent keys, just like forms had its ascendent keys) and I wanted to delete the jobs/forms/parts for a particular user (similar to #1) then would the following work:

delete job, forms, parts 
	from user, job, forms, parts 
		where 	user.user in (13,14) and 
			user.user = job.user and 
			user.user = forms.user and 
			user.user = parts.user and
			job.job = forms.job and
			forms.forms = parts.forms;

(The only thing that looks missing is the relation between job and parts but I'm not sure if that is necessary -- PLEASE ADVISE!!! grazie!)

Drakla

Yup!
If parts doesn't have a relationship with job, and I can't imagine it does, then your query is perfecto.

If a user is being deleted and you want to get rid of all parts relative to them, then you could just use the parts.user part of the query. I.E if when Bagpuss goes to sleep all of his other friends to go sleep, too (delete a user, delete his part) then matching on the user part is all you need. BUT ONLY when there's not something else in the delete group that's unrelated - i.e. the query might form a good match with all the user matches, and you miss out a part of the WHERE for another table, and it ends up deleting a field from there too.

Sorry, I'm blithering (bit drunk)

David_James

I thought I would clarify this for anyone doing multiple table deletes (rather important to know how to do it properly). However, if you aren't using a multi-column primary key schema then this may confuse... Anyways, here is the revision to the above query:

delete job, forms, parts 
	from user, job, forms, parts 
		where 	user.user = 14 and 
			job.job in (1) and 
			user.user = job.user and 
			user.user = forms.user and 
			user.user = parts.user and 
			job.job = forms.job and 
			[b]job.job = parts.job[/b] and <-- this one IS necessary after all
			forms.forms = parts.forms;

I tried it without the above change and it deleted too many records from the parts table. Therefore, the where pattern seems to be:

1. Specify the ids

Relate higher tables to ALL lower tables[/i][/b]

I hope this helps! 🙂 <edit>I think this is what drakla was "blithering", so thanks after all!</edit>

David_James

OK, I just came up against another hurdle. Given the previous posts delete statement, what if there were no records in the parts table or the forms table? How do I write it to handle situations where there MAY or MAY NOT be records in some of the tables?

(I tried this query where there was a job record but no forms or parts records and it didn't delete anything) :bemused:

David_James

BUMP

Please help (or tell me it's not possible). Again, the question is: can I write ONE query that will handle deletes WHETHER or NOT there is data in the lower tables???

Extreme gratitude in advance!!!!

David_James

Well, I visited the mysql.com forums and got an answer. I thought I would post it here if it might help someone else:

First of all this method DOES NOT DELETE ANYTHING IF THERE IS MISSING DATA IN LOWER TABLES:

delete job, forms, parts 
	from user, job, forms, parts 
		where 	user.user = 14 and 
			job.job in (1,3) and 
			user.user = job.user and 
			user.user = forms.user and 
			user.user = parts.user and 
			job.job = forms.job and 
			job.job = parts.job and
			forms.forms = parts.forms;

However this method DOES DELETE ALL APPLICABLE RECORDS EVEN IF THERE IS MISSING DATA IN LOWER TABLES:

DELETE job, forms, parts
	FROM user
		LEFT JOIN job ON job.user = user.user
		LEFT JOIN forms ON forms.user = user.user AND forms.job = job.job
		LEFT JOIN parts ON parts.user = user.user AND parts.job = job.job AND parts.forms = forms.forms
			WHERE user.user = 14 AND job.job in (1,3);