Menu
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?
303
To remove one or more rows in a table: First, you specify the table name where you want to remove data in the DELETE FROM clause. Second, you put a condition in the WHERE clause to specify which rows to remove. If you omit the WHERE clause, the statement will remove all rows in the table.
If it works if all tables have records, try using LEFT JOIN instread of INNER JOIN. Also, You had some mess with Your joins ON conditions. Try it like this: delete relativedata, crawls, stored from relativedata LEFT join crawls on relativedata.
The syntax also supports deleting rows from multiple tables at once. To delete rows from both tables where there are matching id values, name them both after the DELETE keyword: DELETE t1, t2 FROM t1 INNER JOIN t2 ON t1.id = t2.id; What if you want to delete nonmatching rows?
Your eventID in all table will make it work. For deleting records from multiple tables: You could define Foreign Key constraints (which you have defined as EventID) for the other tables that reference the master table's ID with ON DELETE CASCADE. This would cause the related rows in those tables to be deleted.
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; 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.
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
