Questions tagged [delete]
In the database structured query language (SQL), the DELETE statement removes one or more records from a table.
455
questions
101
votes
7
answers
149k
views
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 ...
- 1,112
47
votes
6
answers
57k
views
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 ...
- 573
46
votes
9
answers
11k
views
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 ...
- 885
43
votes
2
answers
101k
views
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 +...
- 619
39
votes
5
answers
85k
views
Methods of speeding up a huge DELETE FROM <table> 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 ...
- 1,885
34
votes
1
answer
38k
views
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 ...
- 1,969
31
votes
2
answers
62k
views
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.
- 10.8k
30
votes
4
answers
130k
views
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 ...
- 402
24
votes
6
answers
44k
views
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. ...
- 551
24
votes
2
answers
18k
views
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 ...
- 11.7k
21
votes
3
answers
139k
views
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 ...
- 349
20
votes
2
answers
4k
views
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/...
- 4,330
19
votes
6
answers
84k
views
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 ...
- 335
18
votes
2
answers
10k
views
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 - ...
- 291
17
votes
3
answers
5k
views
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 ...
- 349
16
votes
2
answers
3k
views
SQL estimates are way off on DELETE statement with Triggers on huge tables
I'm working with Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64) Nov 27 2019 18:09:22 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 ...
- 2,029
16
votes
3
answers
32k
views
Why is truncate DDL?
I have an interview question, which was asked during my interview. I answered the question, but interviewer was not so convinced with my answer. So, anyone please correct me with my understanding ?
Q....
- 1,545
15
votes
3
answers
21k
views
Is there a way to test whether DELETE will fail due to constraints?
I'd like to be able to predict whether a DELETE will run into a constraint violation, without actually performing the delete.
What are my options for doing this? Is there a simple way to do a "...
- 361
15
votes
3
answers
18k
views
Is there any time where we should avoid cascading delete?
In SQL Server 2008, there is a Primary table which is linked to three other child tables by 1 to many relationship. So, we are thinking of using Cascading delete in the primary table, so that all the ...
- 253
15
votes
2
answers
7k
views
Is the system column "ctid" legitimate for identifying rows to delete?
I have a table with hundreds of millions of rows that I need to delete data from.
The existing indexes are the most efficient.
I can however use the existing indexes to find the rows to delete by ...
- 251
14
votes
1
answer
14k
views
Why is DELETE so much slower than SELECT, then DELETE by id?
I have a fairly busy InnoDB table (200,000 rows, I guess something like tens of queries per second). Due to a bug I got 14 rows with (the same) invalid email addresses in them and wanted to delete ...
- 243
13
votes
3
answers
61k
views
Do I need to reindex mysql table after bulk delete?
I have a table in MySQL with a lot of INSERT and SELECT at every second. And there is a bulk delete of some older data once in a day. Do I need to reindex the table after delete? I want to increase ...
13
votes
1
answer
38k
views
MySQL DELETE becomes exceptionaly slow with a large number of rows
When performing a DELETE against a large number of rows in the Prices table, the DELETE becomes progressively slower. If deleting 15,000 rows it runs in about 15 seconds. 20K rows takes 3 or 4 ...
- 265
13
votes
1
answer
1k
views
Clustered index not used in a delete statement
I have a SQL Server table defined as follow
CREATE TABLE [dbo].[Production_Detail]
(
[Id] [bigint] NOT NULL DEFAULT (NEXT VALUE FOR [dbo].[Production_Detail_Seq]),
[Meta_Data_ID] INT NOT NULL ...
- 145
13
votes
1
answer
766
views
Disk file effects of delete and vacuum
I have a very frequently updated table with 240 million rows (and growing). Every three hours 1.5 million rows are inserted and 1.5 million are deleted. When I moved the cluster to a SSD this bulk ...
- 1,135
12
votes
2
answers
16k
views
Can I use table aliases in a MySQL DELETE statement?
This does not work:
DELETE FROM topics AS t , posts AS p USING t INNER JOIN p
WHERE t.id=p.topic_id AND t.id = '5599';
whereas this does:
DELETE FROM topics, posts USING topics INNER JOIN posts
...
- 235
12
votes
1
answer
14k
views
Deleting many rows on a live table in chunks - without locking for too long (PostgreSQL 9.3)
I am new to postgres,
I have a 20 million row table that is on a live server - I need to remove most of the rows but not all. I want to do this without impact to other read/write processes accessing ...
- 1,487
12
votes
2
answers
60k
views
deleting large volumes of rows from table efficiently in SQL Server [duplicate]
I have a table that has gotten a little out of control. I am not a DBA per se but seem to recall that deleting a huge volume of rows in one shot can cause transaction log issues, hamper overall system ...
- 1,257
11
votes
2
answers
10k
views
Large delete query seems to have frozen
We ran a delete query on a database with 1.8bn rows. This delete would delete 1.2bn rows.
In hindsight, we would have broken up this query into 100m at a time but we are in a position where it has ...
- 211
11
votes
4
answers
78k
views
DELETE statement conflicted with the REFERENCE constraint
My situation looks like this:
Table STOCK_ARTICLES:
ID *[PK]*
OTHER_DB_ID
ITEM_NAME
Table LOCATION:
ID *[PK]*
LOCATION_NAME
Table WORK_PLACE:
ID *[PK]*
WORKPLACE_NAME
Table INVENTORY_ITEMS:
ID ...
- 111
11
votes
3
answers
17k
views
Delete millions of rows from a SQL table
I have to delete 16+ millions records from a 221+ million row table and it is going extremely slowly.
I appreciate if you share suggestions to make code below faster:
SET TRANSACTION ISOLATION LEVEL ...
- 225
10
votes
2
answers
89k
views
Delete an oracle instance, the right way
I want to delete an oracle instance (oracle 10.2.0.4.0) created in the AIX 6.0 OS.
I know i can delete all the dbf and ctl files in the Terminal but i thinks that's no the best way to do it. I think ...
10
votes
3
answers
18k
views
Delete rows older than x days without locking table
we have a quite big MS SQL database with millions of rows.
I created a simple script to delete rows older than 1 month, but this seems to lock the table and creates trouble for the application.
The ...
- 101
10
votes
4
answers
15k
views
Bulk Delete for Large Table in MySQL
I have a Notification table contains about 100 million rows host in Amazon RDS with 1000 IOPS, and I want to delete those rows older than one month.
If I do DELETE FROM NOTIFICATION WHERE CreatedAt &...
- 133
10
votes
3
answers
2k
views
What are best practices for safely permanently deleting a database?
We have an "organic" environment, meaning people piled code on code for ten years with minimal oversight or documentation. The server I use has several databases which I believe are no longer being ...
- 5,967
9
votes
4
answers
14k
views
How to improve InnoDB DELETE performance?
So I have this audit table (tracks actions on any table in my database):
CREATE TABLE `track_table` (
`id` int(16) unsigned NOT NULL,
`userID` smallint(16) unsigned NOT NULL,
`tableName` ...
- 23.5k
9
votes
4
answers
51k
views
Truncate with where clause
Can I use truncate command with a where clause? I need to remove specific rows from several tables.
How can I delete specific data from the entire database?
SELECT DimEmployee.[FirstName], ...
- 898
9
votes
2
answers
5k
views
MySQL: delete...where..in( ) vs delete..from..join, and locked tables on delete with subselect
Disclaimer: please excuse my lack of knowledge about database internals. Here it goes:
We run an application (not written by us) which has a big performance problem in a periodic cleanup job in the ...
- 141
9
votes
1
answer
8k
views
PostgreSQL 9.5.2 DELETE taking forever
I have a seemingly simple delete:
DELETE FROM table WHERE added_at < '2017-08-14'
on a table with ~20k rows (w ~10k affected rows), but it has been running for 2000+ seconds.
SELECT * FROM table ...
- 193
8
votes
4
answers
3k
views
Delete statement not responding to Kill session command
Four days ago a user ran the command below on a table with 400,000,000 rows. It's still running and the log file is increasing in size.
delete from [table-name]
This table does have a foreign key ...
- 183
8
votes
3
answers
7k
views
Deleting all duplicates
I am trying to delete all the duplicates but keeping single record only (shorter id).
Following query deletes duplicates but take lot of iterations to delete all copies and keeping original ones.
...
- 443
8
votes
1
answer
3k
views
Slow DELETEs of LOB data in SQL Server
I have a tables for logging, and a stored procedure to purge old data that has very slow DELETE performance, beyond what makes sense to me. I am looking for how to modify the tables or DELETE ...
- 255
7
votes
3
answers
40k
views
How to delete duplicate records in MySQL, in a table without IDs?
I need to delete the duplicate records in this table. However, there is no id for each row.
Example Data
product
amount
quantity
table
2000
5
chair
300
25
TV
30000
4
bike
300
25
table
2000
5
...
- 173
7
votes
3
answers
7k
views
Deleting data from a large table with replication
I need to delete all the rows from a given table. The table contains millions or records. The master database is being replicated to several slaves and I wish to do that without creating a replication ...
- 173
7
votes
3
answers
8k
views
What is the best approach to run a DELETE query on 92K of 4 million rows?
Options that have occurred to me:
One big delete statement. For example:
delete from x where (y = 'x' and x = 'y') or (y = 'a' and x = 'b')
Same as above, but chunk in smaller pieces, 10k at a time?...
- 173
7
votes
1
answer
1k
views
Deleting millions of records on SQL Server 14.0
I have two tables containing 200 Millions of records each.
I have to delete from them, approximately 70 Millions of records, based on an integer value in a column.
I'm deleting them in chunks of 4000,...
- 231
7
votes
2
answers
709
views
What to do after deleting a million records
We are implementing a new feature in our system that will cause about a million records (each record is tiny, basically a GUID, a date, and four smallint fields) to be purged from a table every night. ...
- 561
7
votes
2
answers
7k
views
How to implement soft deletes?
What is your method of implementing soft deletes in a database?
Ideally the solution would allow for
decent performance on large tables
take relationships into account
take unique keys into account
...
- 8,463
7
votes
3
answers
9k
views
Delete data from a billion row table based on where clause
I need to delete about 400 million rows from a 1.3 billion row table based on an indexed date field.
The table is about 800 GB in size.
What is the most efficient way to delete the data without ...
- 342
6
votes
1
answer
7k
views
Performance tuning a cascading delete with many foreign keys
I have a delete query that is taking a long time. Looking at the execution plan, I see that most of the estimated cost in the delete query is in a section of the data model that had a lot of data (say,...
- 163