Questions tagged [delete]

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

Filter by
Sorted by
Tagged with
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 - ...
Lorenzo Polidori's user avatar
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 ...
Matt Wilko's user avatar
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 ...
tuseau's user avatar
  • 1,885
101 votes
7 answers
148k 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 ...
jd.'s user avatar
  • 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 ...
Justin Rhyne's user avatar
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` ...
Derek Downey's user avatar
  • 23.5k
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/...
孔夫子's user avatar
  • 4,330
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....
Ravi's user avatar
  • 1,545
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 ...
ruelloehr's user avatar
  • 251
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 ...
user2992455's user avatar
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. ...
Gary Lindahl 's user avatar
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 ...
Abacus's user avatar
  • 255
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 ...
zvikico's user avatar
  • 173
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 ...
Neil McGuigan's user avatar
6 votes
1 answer
3k views

Why is the 'DELETE' operation on a PostgreSQL database table unusually very slow?

I have encountered a significant performance issue when executing the 'DELETE' operation on a PostgreSQL database table. The execution time for deleting 15488 records was 79423.768 ms which is very ...
ShwetaJ's user avatar
  • 163
3 votes
1 answer
34k views

Delete from table where multiple fields match select subquery from other table

I want to delete an entry in a table where multiple fields match the results of another select subquery which takes data from another table. This is what I have so far, though it doesn't work: ...
Oskar Persson's user avatar
3 votes
2 answers
13k views

Can I do anything about a very long running KILL command?

I've got a table with 103M+ records in it and takes up 70GB on the disk. I decided to DELETE (in one statement) 70% of the records. As expected, this took an awfully long time to run. I decided to ...
ChrisW's user avatar
  • 131
2 votes
2 answers
10k views

remove duplicate rows in mysql table that does not contain primary key

I have a table item having just one column name:- name ----- toys shirt mobile Shirt speaker Toys .... .... I am trying to delete duplicate rows except one duplicated record with the help of below ...
neeraj's user avatar
  • 260
-1 votes
1 answer
2k views

How To Delete Record From dba_audit_trail in Oracle 11gR2

I want to delete records older than one month from AUD$ table, but I have to do this on dba_audit_trail, because I have to do this job according dba_audit_trail's timestamp. I can't find any clear ...
jrdba's user avatar
  • 55
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 ...
Hassan Baig's user avatar
  • 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.
Basil Bourque's user avatar
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 ...
Coding Gorilla's user avatar
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 ...
Ramesh's user avatar
  • 253
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 ...
Dina's user avatar
  • 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 ...
JoeGeeky's user avatar
  • 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 ...
Graeme's user avatar
  • 211
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 ...
Jon of All Trades's user avatar
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], ...
l.lijith's user avatar
  • 898
6 votes
1 answer
16k views

Releasing disk space by using DELETE or TRUNCATE?

More than a week ago, I deleted all the rows in a postgresql table (not via truncate, instead with delete from ...). Select count (*) reveals table rows are now 0. When I now run queries to query ...
Hassan Baig's user avatar
  • 1,969
6 votes
3 answers
25k views

MySQL: Delete all but last N records

Consider the following table: mysql> DESCRIBE pixels; +---------------+-------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default ...
Adam Matan's user avatar
  • 11.7k
6 votes
1 answer
11k views

Delete using a composite key

I am trying to wrap my head around creating a delete statement for a table with a composite key. I would like to create something like: DELETE FROM table_example1 WHERE COLUMN1, COLUMN2 ...
Reaces's user avatar
  • 2,601
5 votes
2 answers
10k views

Solving ON DELETE CASCADE cycles with triggers on MS SQL Server

I have code that is working fine in PostgreSQL and I now have to port it to MS SQL Server. It involves tables with potential cycles on delete/update events and SQL Server is complaining about it: -- ...
Matthieu's user avatar
  • 213
5 votes
3 answers
6k views

Optimizing DELETE Query on MySQL MEMORY Table

I run a large forum which maintains a MySQL database for backend data storage. The 'session' table tracks logged in users & guests. It is currently about 100k records, so not really that large. ...
Dave Drager's user avatar
5 votes
1 answer
597 views

Standard Behaviour for mix of 'ON-DELETE-CASCADE' and 'ON-DELETE-RESTRICT' constraints in Mysql

In mysql 5.6, Consider these 2 examples creating relationships between A, B, C and D. Example 1 CREATE TABLE `a` ( id INT UNSIGNED NOT NULL, PRIMARY KEY (id) ) ENGINE = INNODB; CREATE TABLE ...
Dinesh Kumar's user avatar
4 votes
3 answers
37k views

INSERT and DELETE in single operation (moving from one table to other)

Stemming from this question, I am going to use that complex sort's results to decide which rows' data to move from one table to another. Though I have not yet tried it, I think there should be no ...
insaner's user avatar
  • 189
4 votes
1 answer
7k views

Does InnoDB ever release unused disk space?

A large MySQL association table with 500M rows recently ballooned into 1B rows due to a bug (now fixed). Disk usage went from ~130GB to 240GB. Unfortunately there is not room on this server for any ...
Ryan's user avatar
  • 272
4 votes
2 answers
1k views

Managing and Removing data from huge table

I have the following scenario: A huge table, that is also the most use table by far in the system. By huge I mean it is huge in number of rows and also huge in actual disk size, because it has 2 byte ...
Luiz Eduardo Simões's user avatar
4 votes
2 answers
46k views

How do I uninstall the old version of Postgres on CentOS 7?

I just installed Postgres 9.6 on CentOS 7. However when I run [root@server tmp]# /usr/bin/psql --version psql (PostgreSQL) 9.2.23 It still seems to be pointing to my old version. How do I ...
Dave's user avatar
  • 693
4 votes
2 answers
1k views

DELETE on a heap table

Is there any way to deallocate empty pages on a heap table during a delete operation other than putting an exclusive lock on the table or rebuilding it? None of these options are acceptable for my ...
Nizami Khalilbayli's user avatar
4 votes
2 answers
9k views

What to use, CASCADE DELETE rule or ON DELETE trigger for one to many constraint?

I would like to ask about how to handle this relationship. For better view I've tried to simplify and convert my situation into how I think StackOverflow question voting has (sorry if not :-) I would ...
TLama's user avatar
  • 143
4 votes
1 answer
7k views

Why is SQL Server using a clustered index scan for a self referencing FK cascade delete

I have made an example to allow me to show what my issue is: Setup: CREATE TABLE [dbo].[Test]( [TestId] [bigint] IDENTITY(1,1) NOT NULL, [ParentTestId] [bigint] NULL, CONSTRAINT [PK_Test] ...
Vaccano's user avatar
  • 2,496
3 votes
1 answer
7k views

Delete data from huge InnoDB table from MySQL Master and Slaves

We have some huge tables in MySQL database, We have already archived/deleted data older than 2012 in 2013, Now we need to archive/delete data older than 2013 means we need to archive data for year ...
Abdul Manaf's user avatar
  • 9,677
3 votes
1 answer
11k views

Cascade deletes from multiple tables

I have a fairly standard restaurant model with a restaurant table in a PostgreSQL DB. All restaurants have ratings (which consist of average, vote_count and vote_sum) but in order to avoid repeating ...
kelsohmm's user avatar
3 votes
1 answer
112 views

Single statement or batched delete?

I need to delete 6.3 million records from 25 tables in production. I have the following two queries, but I don't know which one will be best: delete jc from Jtable jc join J1Table j on j.JobId = jc....
backtrack's user avatar
  • 131
2 votes
1 answer
7k views

MySQL master master replication, safe to delete rows on both masters?

We have a MySQL (v5.1.61) cluster with two masters (and two slaves). We have a table logs with a primary key column logID. This log table grows rather huge, and we'd like to delete data from it. So I'...
KajMagnus's user avatar
  • 1,209
2 votes
2 answers
5k views

DELETE seems to hang

The following command: DELETE FROM hours WHERE id IN (SELECT id FROM holdkey WHERE persons_id=60) seems to hang indefinitely. However, the corresponding SELECT * command completes in a fraction of a ...
Michael Jørgensen's user avatar
2 votes
1 answer
4k views

I have delete database from mysql but storage is not freed

I have deleted multiple databases (schema) from mysql on amazon RDS but the storage is not freed, is there anything else I should do to free up the storage? I have a mysql database that the total ...
Navnav's user avatar
  • 143
2 votes
2 answers
8k views

Massive MySQL delete from CSV file

To insert massive CSV file into MySQL, there is LOAD DATA INFILE, but is there any similar command to delete massive entries with keys stored in a massive CSV file? If not, what would you see as the ...
Syffys's user avatar
  • 121
1 vote
1 answer
6k views

How do I force the delete of users from my database?

I am making a clean backup of our development database for easy fresh spin-ups of the database. This database has about 200 test users that should be deleted to make it truly clean, but the problem is ...
Cyndi Baker's user avatar
1 vote
1 answer
3k views

Delete large number of rows and change index - what steps in which order and things to watch out for

We daily collect data with multiple records per second. The table my_xpa_data has currently close to 100 Million rows and is growing fast. table size around 8 GB (used to be 4 GB a couple of days ago)...
surfmuggle's user avatar