Questions tagged [delete]
In the database structured query language (SQL), the DELETE statement removes one or more records from a table.
61
questions
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
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
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
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 ...
- 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
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
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
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
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
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
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
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
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
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
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 ...
- 163
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 ...
- 131
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:
...
- 255
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 ...
- 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 ...
- 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 ...
- 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
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
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
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
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
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
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 ...
- 11.7k
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 ...
- 1,969
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
...
- 2,601
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. ...
- 163
5
votes
1
answer
598
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 ...
- 664
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:
-- ...
- 233
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 ...
- 693
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 ...
- 272
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 ...
- 189
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 ...
- 143
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 ...
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] ...
- 2,496
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 ...
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 ...
- 33
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....
- 131
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 ...
- 9,677
2
votes
1
answer
5k
views
Postgres taking 3h to DELETE CASCADE
I have a Postgresql 9.3 with a "mother" table containing items, and a number of "child" tables containing parts of different kinds (e.g. item "AAA" is composed of 1000 parts "P1", 800 parts "P2", 40 ...
- 233
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 ...
- 121
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'...
- 1,209
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 ...
- 143
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 ...
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)...
- 219