[RESOLVED] Deleting from multiple tables in a non-relational database with one query?
I am working with a client's database that is, unfortunately, NOT relational. What I am trying to do is delete data from 3 different tables based on one of the table's columns having a certain value. I have got the select statement already written and it works great, I just can't figure out how to do a delete in the same way.
I should mention that the only field that is the same in each table is a field called Unique_patient_identifier. It's not a foreign key, index, or anything. I've just been told it's the only field that should be used to join the 3 tables together. Also, each table does have an ID column that is a primary key, but it is in no way relational to the other tables and never matches up
Here is my select query that I have working:
SELECT Trailer.*, Header.*, Detail.*
ON Trailer.Unique_patient_identifier = Header.Unique_patient_identifier
ON Trailer.Unique_patient_identifier = Detail.Unique_patient_identifier
WHERE Trailer.Patient_balance = '.00'
Is it possible to delete everything in these 3 tables at once using the '.00' patient_balance condition?
Please share the solution.
I don't think you can delete from multiple tables without using innoDB, even then it seems risky to me.
I would have suggested saving the Unique_patient_identifier in a variable, then checking if there's a scenario where you would delete records.
If there was I'd send three delete queries.
Sorry. Yes, I had found out that it's not possible to delete from multiple tables like you said. I happen to be working with MSSQL so things are a little different for me this time around since I usually do MySQL.
I am not very advanced with SQL and am still learning about transactions, stored procedures, and variables, but what I ended up doing what just making 3 different queries that looked like this:
DELETE FROM Header WHERE ID IN
(SELECT ID FROM Trailer WHERE Patient_balance = '.00')
But the problem I've NOW run into is the database is set up with foreign key constraints which is giving me problems deleting from a couple of the tables. I figured I would either try to read up more about constraints or ask how to get around that in another post.
I've read around the net that if you don't want to disable/delete the foreign keys and the constraints, you have to delete all the child data first and then work your way up deleting the parent data. The problem is this database has quite a few constraints and it would be rather annoying to find each constraint, delete the child rows I want to delete, and repeat.
I've read something about tables that can be set up to do cascade on delete or something, but a previous company had worked on this database and I'm really still learning about the structure of it and how MSSQL differs from MySQL. Unfortunately it's just me working on it and I am unable to talk to any of the previous developers.
Also, I forgot to mention I'm currently doing everything in SQL Server Management Studio.