In the database structured query language (SQL), the DELETE statement removes one or more records from a table.
28
votes
7answers
699 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 ...
13
votes
2answers
701 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 ...
12
votes
3answers
863 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 ...
10
votes
4answers
9k 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 ...
10
votes
3answers
10k 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 ...
8
votes
4answers
4k 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 ...
8
votes
1answer
262 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 ...
7
votes
2answers
2k 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 ...
7
votes
3answers
621 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.
...
7
votes
3answers
237 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 ...
6
votes
3answers
1k 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 ...
5
votes
2answers
714 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 - ...
4
votes
1answer
204 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 ...
4
votes
2answers
3k views
Oracle 10g: Properly using ora_rowscn to detect table row changes (ie, inserts, updates, deletes)
Some research into checking when a table's records were last updated, modified, or deleted has lead me to the pseudo column known as ora_rowscn.
First, I do this:
select max(ora_rowscn) from ...
4
votes
2answers
106 views
DELETE LOW_PRIORITY row visibility
I have a large MyISAM table on MySQL 5.5(Windows XP x64) on which I will have to run DELETE LOW_PRIORITY queries. Does DELETE LOW_PRIORITY make the rows invisible to SELECT statements immediately and ...
4
votes
2answers
4k views
can I resolve a deadlock with the rowlock hint?
I have a large delete stored proc and I've reproduced a deadlock in a situation where the deletes would not have deleted anything.
It looks like the part of the stored proc that hit a deadlock was ...
4
votes
4answers
37 views
Fast way of delete MySQL record
I have a 1.2 million record in a database table( it use index column also)
I want to delete old 1 million record. I try to do it using PhpMyadmin but it take more time.
DELETE FROM `table` WHERE ...
4
votes
1answer
1k 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 ...
4
votes
2answers
430 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
...
4
votes
2answers
166 views
Delete all rows within transaction: what would happen to other transactions?
I currently have a function in PostgreSQL that creates a temp table, fills it, performs some tricky selects and returns results. postgresql's temp tables are local to functions, so this helps me avoid ...
3
votes
3answers
698 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. ...
3
votes
3answers
273 views
How to delete large amount of data from a sql table?
We ran out of disk space and we have a table with about 10million records.
We'd like to delete the old records which are about 2million.
What are the impact of just deleting these using a simple ...
3
votes
2answers
3k 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 ...
3
votes
1answer
150 views
Why is truncate DDL?
I have an interview question, which being asked during my interview. I answered the question, but interviewer was not convinced with my answer. So, please let me know the what would be the correct ...
3
votes
1answer
307 views
Oracle explain for cascading delete is wrong
My deletes in Oracle 11 are very slow. The tables are linked by foreign keys and every foreign key constraint has a delete cascade set on it.
If I hit the explain button on a statement like this ...
3
votes
2answers
174 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 ...
2
votes
3answers
2k views
Clear all tables with one DELETE
Is it possible to clear all tables with one request?
I have tried the following with no result.
DELETE a,b,c,d,e,f,g,h,i,j,k,l,m,n,o
FROM jos_bet_details as a, jos_bet_1x2 as b, jos_bet_1x2_best as ...
2
votes
2answers
67 views
Delete all highscores from a certain country
I have two tables:
tbl_players
tbl_highscores
I need to delete all highscores that are registered from a certain country. My problem is that the language are set per player, rather than by ...
2
votes
2answers
98 views
Inserting data to a table which is being cleaned up by SQL job at the same time
Our app uses a table in a database and we have a Sql job set up to run overnight which executes a stored procedure which just deletes data older that from the table.
DELETE FROM
MyTable
...
2
votes
1answer
313 views
Delete rows in a table that has multiple FKs
I need to delete all the rows in the ContentData table that have the word 'DV95' in the htmldata column.
Here is what I am using:
BEGIN TRANSACTION
DELETE Contentdata
FROM Contentlinks INNER JOIN ...
2
votes
2answers
413 views
Oracle: deleting data from tables leads to data file fragmentation?
When randomly deleting ~5GB from a ~10GB Oracle table, I suspect that disk space is left in fragments. Is there a way to know about the disk state and any query to defragment or shrink the space?
2
votes
4answers
132 views
Delete the N oldest entries grouped by multiple columns
I´d like to delete the oldest entries of my database, but in each category shall remain at least N words of each char.
Structure of TABLE words
id | word | char | category
For example I have the ...
2
votes
1answer
876 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 ...
2
votes
1answer
257 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 ...
2
votes
1answer
167 views
Error deleting more than 1 row
I have a stored procedure that is supposed to delete several rows from a table. It does not work and gives me the error:
The transaction ended in the trigger. The batch has been aborted
I tried ...
2
votes
1answer
132 views
clustered and covering index ignored on delete statement. Table scan occurs
Why would SQL Server 2005 find it more efficient to perform a table scan instead of using the available clustered index on the primary key (and only the primary key)?
DISCLAIMER:
There is also a ...
2
votes
2answers
113 views
How can I delete repeated rows?
I have a system that gathers RSS from many sites including UTF-8 based sites (non-english sites). I have tried to avoid repeated rows but I have repeats in my table.
RSS_items (id(auto_increment) ...
2
votes
1answer
246 views
Postgresql revert back database value
I have postgresql database.
My table name is tblvoippolicy. Now, problem is by mistake i have deleted some records from table.
When I get the data file using following commands.
select * from ...
2
votes
0answers
40 views
What is the equivalent of a “Certificate of Destruction” when using a cloud-hosted database?
We have a Rails application hosted by Heroku and using an Heroku Postgres database. Some of the information we store is considered to be sensitive by our users, and we've been asked if we can provide ...
1
vote
2answers
1k views
MySQL: Delete all but last N records
Consider the following table:
mysql> DESCRIBE pixels;
+---------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default ...
1
vote
2answers
57 views
Approaches for deleting unnecessary records from tables
We have a database which stores the temperatures of 20 thermometer every 7 seconds. We want to delete all records in the way that every minute holds just one tempereature instead of 8 which are older ...
1
vote
1answer
56 views
Mysql setting a record as deleted or archive
Is there any way to omit some records in mysql select statement and not deleting them? We can easily add a column for example deleted and set it to 1 for deleted ones and keep them but the problem is ...
1
vote
1answer
297 views
Delete by primary key takes too long
What would make a query that deletes a row using the primary key of the table .. take too long ?
This is a table with about 1.4M rows that gets .. 70%/30% read/write .
I don't know where to look.
...
1
vote
2answers
1k views
MySQL - automatically update all references to specific table to use on delete cascade?
I have a specific table for one of my software packages on a MySQL 5.1 box which I need to drop a few hundred entries. These are user names with names in uppercase, the application is case specific ...
1
vote
2answers
289 views
Deny insert/delete for all users in SQL Server 2005
In our database we have about 20 users, that is used be some program (and users are added with this program). Now there was new requirement to use 2 database and do not allow delete data from one ...
1
vote
1answer
60 views
Will deleting data in master automatically delete data in slave?
I have data replication setup on MySQL. If I delete data on the master, will replication know data was deleted and automatically delete the same data on slave?
1
vote
1answer
222 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
vote
1answer
190 views
mysql query - how to delete duplicates based on highest int?
I'm running a reverse auction website and I need to delete the duplicate bids and keep only the highest amount . lastPriceBid_rounded is the "amount". The duplicate is defined by the userId.
...
1
vote
1answer
205 views
Inherit audit columns and triggers
Background
I have a database with dozens of tables, some of which should have audit columns.
Problem
I'd like to avoid the drudgery of creating audit columns manually, and write the update and ...
1
vote
2answers
2k 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 ...
