Tagged Questions

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

learn more… | top users | synonyms

1
vote
1answer
450 views

Optimization: Delete with Top and minimize the where clause date range

I have a delete statement that get run repetitive until all the data are removed. Delete TOP (1000) From Log Where CreationDate < '2011-Dec-31' AND pKey NOT IN (...) I am considering of ...
2
votes
1answer
771 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 ...
6
votes
3answers
2k 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 ...
12
votes
3answers
2k 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 ...
7
votes
3answers
1k 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. ...
5
votes
2answers
7k 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 ...
5
votes
3answers
6k 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 ...
2
votes
3answers
5k 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 ...
18
votes
3answers
26k 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 ...
7
votes
3answers
389 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 ...
10
votes
2answers
3k 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 ...
9
votes
4answers
10k 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 ...
11
votes
5answers
22k 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 ...
28
votes
7answers
838 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 ...