Take the 2-minute tour ×

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?

share|improve this question
Is it mysql or postgres? You tagged both... –  bfavaretto Apr 13 '12 at 17:23
Welcome to Stack Overflow! I always like to let newcomers know that appreciation is shown via upvotes (once you have 15+ points) and accepting answers (check next to answer). The faq is useful, especially the How to Ask section –  Justin Pihony Apr 13 '12 at 18:02

5 Answers 5

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)

    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

    (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;
share|improve this answer

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:


    DELETE FROM point WHERE evt_id = 1139
    RETURNING pt_id
WHERE trace.pt_id = x.pt_id;

DELETE FROM magnitude WHERE evt_id = 1139;

DELETE FROM event WHERE evt_id = 1139;


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.

share|improve this answer

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.


share|improve this answer

I assume that the id's in the different tables correspond and are used for linking. I also assume you want to delete all the trace points allthough they are only indirectly linked to evt_id

You can delete your records from all tables as follows:

DELETE event , magnitude, trace, point
FROM event left join magnitude on event.evt_id = magnitude.evt_id
   left join point on event.evt_id = point.evt_id
       left join trace on point.pt_id = trace.trace_id
where event_id=1139
share|improve this answer
This will only work in mysql, and the OP has re-tagged this question from mysql and postgresql to only postgresql –  Justin Pihony Apr 13 '12 at 18:00
$delete =  1139;
$query = "DELETE FROM event, magnitude, point WHERE evt_id = '$delete'";
mysql_query($query, $con);
$query = "DELETE FROM trace WHERE pt_id = '$delete'";

or just paste that statement into an .sql file and run it, or into your sql software query window, or place it in a .php file and run on the server.

share|improve this answer
As 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 –  Justin Pihony Apr 13 '12 at 17:18
The database is on the server. How do i run this query file? –  user1202766 Apr 13 '12 at 17:18
Yes 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 ! –  user1202766 Apr 13 '12 at 17:20
@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 –  bfavaretto Apr 13 '12 at 17:25
@user1202766 Ahhh, I did not notice the mySQL tag since the last tag was postgresql...which does not...please fix your tags :) –  Justin Pihony Apr 13 '12 at 17:29

Your Answer


