How to delete data from multiple tables?
Solution 1
If you have control over your schema, I would make the schema use cascading deletes.
From the article (the more pertinent portion translated for your example)
CREATE TABLE point
(
pt_id integer PRIMARY KEY,
evt_id integer REFERENCES event ON DELETE CASCADE
)
If you have cascades set up, then you can just delete from the main event table and all the other tables will be cleaned up automatically
Otherwise, you need to delete all of the references first, then you delete the main table. You should do this in one transaction to keep data consistent
BEGIN;
DELETE FROM trace WHERE EXISTS
(SELECT 1 FROM point WHERE evt_id = 1139 AND trace.pt_id = point.pt_id);
DELETE FROM point where evt_id = 1139;
DELETE FROM magnitude where evt_id = 1139;
DELETE FROM event where evt_id = 1139;
COMMIT;
Solution 2
The only non-trivial element in your question are deletes from the table trace
. I guess it is safe to assume trace.pt_id
is referencing point.pt_id
?
Either you define a foreign key with ON DELETE CASCADE
and just forget about the table trace
(as already pointed out by @kevin) or you have to take care of depending rows manually.
Since PostgreSQL 9.1 you can use data-modifying CTEs:
BEGIN;
WITH x AS (
DELETE FROM point WHERE evt_id = 1139
RETURNING pt_id
)
DELETE FROM trace
USING x
WHERE trace.pt_id = x.pt_id;
DELETE FROM magnitude WHERE evt_id = 1139;
DELETE FROM event WHERE evt_id = 1139;
COMMIT;
The RETURNING clause of the DELETE FROM point
returns all affected pt_id
- those are in turn used to delete all corresponding rows from trace
.
You did not mention whether concurrency is a problem in your case. If it is, and if you want to avoid possible results in the short time window in between deletes where rows for evt_id = 1139
are present in one table while already deleted from another, wrap it all into a transaction.
If that is no concern to you, ignore BEGIN
and COMMIT
.
To avoid deadlocks always use the same sequence of delete statements (in all concurrent transactions). This avoids situations where one transaction would start deleting in one table, the next in the other table and then each would wait for the other.
Solution 3
If the rows you want to delete can be envisioned as a hierarchy, you could define FOREIGN KEY constraints for the relationships with the ON DELETE CASCADE clause. Deleting the row at the top will then cascade down and delete them all.
http://www.postgresql.org/docs/9.1/static/ddl-constraints.html#DDL-CONSTRAINTS-FK
Related videos on Youtube
user1202766
Updated on May 24, 2020Comments
-
user1202766 almost 4 years
I have these tables:
event (evt_id, evt_code, reg_id) magnitude (mag_id, evt_id, value) trace (trace_id, pt_id) point (pt_id, evt_id)
I want to delete all rows from all tables related to
evt_id=1139
.
How do I do it? -
Justin Pihony about 12 yearsAs far as I know, you can't delete from multiple tables like that (but I could be wrong). However, at minimum
trace
does not have an evt_id column -
user1202766 about 12 yearsThe database is on the server. How do i run this query file?
-
user1202766 about 12 yearsYes thats true trace doesnt have an evt_id column but has pt_id which is from the point table which has evt_id column..that is confusing !
-
bfavaretto about 12 years@JustinPihony, I didn't know that either, but it looks like mysql does support that - dev.mysql.com/doc/refman/5.5/en/delete.html
-
Justin Pihony about 12 years@user1202766 Ahhh, I did not notice the mySQL tag since the last tag was postgresql...which does not...please fix your tags :)
-
squarephoenix about 12 years@user1202766 place the above function along with a db connect in a php file and run it on your server. also, google heidisql.
-
Justin Pihony about 12 yearsThe fix is valid, however the OP has updated the tag to point that this is indeed postgresql and not mysql. So, this will not work
-
Justin Pihony about 12 yearsThis will only work in mysql, and the OP has re-tagged this question from mysql and postgresql to only postgresql