Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [delete]

In the database structured query language (SQL), the DELETE statement removes one or more records from a table.

In the database structured query language (SQL), the DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.

Questions with the delete tag regards deletion of records, columns, tables, files and other content or structure using SQL statements or GUI-operations.

More to read: http://en.wikipedia.org/wiki/Delete_(SQL)

467 questions
116
votes
7 answers

Very slow DELETE in PostgreSQL, workaround?

I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign keys referencing the main schema and not the other…
jd.
  • 1,262
  • 2
  • 9
  • 6
47
votes
5 answers

I need to run VACUUM FULL with no available disk space

I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is that I'm not sure what will happen if I run VACUUM…
Justin Rhyne
  • 573
  • 1
  • 4
  • 5
46
votes
9 answers

How should deletions be handled in the database?

I'd like to implement an "undelete" feature in a web application such that a user can change her mind and recover a deleted record. Thoughts on how to implement this? Some options I've considered are actually deleting the record in question and…
Abie
  • 885
  • 1
  • 8
  • 9
45
votes
2 answers

Most efficient way of bulk deleting rows from postgres

I'm wondering what the most efficient way would be to delete large numbers of rows from PostgreSQL, this process would be part of a recurring task every day to bulk import data (a delta of insertions + deletions) into a table. There could be…
tarnfeld
  • 639
  • 2
  • 6
  • 7
39
votes
5 answers

Methods of speeding up a huge DELETE FROM with no clauses
Using SQL Server 2005. I am performing a huge DELETE FROM with no where clauses. It's basically equivalent to a TRUNCATE TABLE statement - except I'm not allowed to use TRUNCATE. The problem is the table is huge - 10 million rows, and it takes…
tuseau
  • 1,895
  • 5
  • 18
  • 18
37
votes
2 answers

DELETE rows which are not referenced in other table

I have two tables in a PostgreSQL 9.3 database: Table link_reply has a foreign key named which_group pointing to table link_group. I want to delete all rows from link_group where no related row in link_reply exists. Sounds basic enough but I've been…
Hassan Baig
  • 2,079
  • 8
  • 31
  • 44
32
votes
4 answers

How to find out who deleted some data SQL Server

My boss had a query from a customer yesterday asking how they could find out who deleted some data in their SQL Server database (it is the express edition if that matters). I thought this could be found from the transaction log (providing it hadn't…
Matt Wilko
  • 422
  • 1
  • 4
  • 7
31
votes
2 answers

Automatic aging-out (deletion) of old records in Postgres

Does Postgres have any features to support aging-out old records? I want to use Postgres for logging, as a sort of queue, where records (log events) older than two weeks are automatically deleted.
Basil Bourque
  • 11,188
  • 20
  • 63
  • 96
24
votes
6 answers

DELETE command not completing on 30,000,000 row table

I have inherited a database and am looking to clean and speed it up. I have a table that contains 30,000,000 rows, many of which are junk data inserted due to an error on behalf of our programmer. Before I add any new, more optimized indexes, I…
bafromca
  • 551
  • 1
  • 3
  • 9
24
votes
2 answers

Trigger: move deleted rows to archive table

I have a small (~10 rows) table called restrictions in my PostgreSQL database, where values are deleted and inserted on a daily basis. I would like to have a table called restrictions_deleted, where every row that is deleted from restrictions will…
Adam Matan
  • 12,079
  • 30
  • 82
  • 96
21
votes
3 answers

MySQL - Delete row that has a foreign key constraint which reference to itself

I have a table in which I store all the forum messages posted by the users on my website. The messages hierarchy strucrue is implement using a Nested set model. The following is a simplified structure of the table: Id (PRIMARY KEY) Owner_Id…
Alon Eitan
  • 349
  • 1
  • 4
  • 13
20
votes
1 answer

Why does DELETE leave a lingering effect on performance?

At the end is a test script for comparing the performance between a @table variable and a #temp table. I think I've set it up correctly - the performance timings are taken outside of the DELETE/TRUNCATE commands. The results that I am getting are…
孔夫子
  • 4,330
  • 3
  • 30
  • 50
19
votes
6 answers

Best way to delete very large recordset in Oracle

I manage an application which has a very large (nearly 1TB of data with more than 500 million rows in one table) Oracle database back end. The database doesn't really do anything (no SProcs, no triggers or anything) it's just a data store. Every…
Coding Gorilla
  • 335
  • 1
  • 3
  • 10
18
votes
2 answers

Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios

I need to keep track of deleted items for client synchronization needs. In general, is it better to add a tombstone table and a trigger that tracks when a row was deleted from the server database - basically adding a new row to the tombstone table…
Lorenzo Polidori
  • 291
  • 1
  • 2
  • 5
17
votes
3 answers

Slow deletion of records when a trigger is enabled

Thought this was solved with the link below - the work around works - but the patch doesn't. Working with Microsoft support to resolve. http://support.microsoft.com/kb/2606883 Ok so I have an issue that I wanted to throw out to StackOverflow to…
tsells
  • 349
  • 3
  • 10
1
2 3
…
31 32