delete records

Question

Post reply

delete records

kumar99ms

SSCommitted

Points: 1584
More actions
August 20, 2008 at 4:42 am

#72717

Query to delete, all the records from a table before half an hour,…
Delete * from table where condition=100 and …..what we have write here…for time…
Regards,

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

karthik M One Orange Chip Points: 29139 More actions · Answer 1

TRUNCATE may help for your need.

before half an hour,…

You have to frame your question better. Also please post your requirement clearly.

karthik

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 2

kumar99ms (8/20/2008)
Query to delete, all the records from a table before half an hour,…
Delete * from table where condition=100 and …..what we have write here…for time…
Regards,

What is the schema of your table? Could you list us the columns that contain the time information? If you have nothing in the table to keep track of the time information, then you will not be able to do this, and will need to rely on your backups (there is point in time recovery in the enterprise edition of SQL Server, so you could restore the information as of half an hour ago into a separate database)

Another thing to do: first use a select statement to see if the rows deleted are the rows you indeed want to delete.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

SuperDBA-207096 SSCrazy Eights Points: 8176 More actions · Answer 3

truncate won't work if you are only trying to delete some of the records... it will empty the table completely.

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 4

karthikeyan (8/20/2008)
TRUNCATE may help for your need.
You have to frame your question better. Also please post your requirement clearly.

While I agree with Karthik that TRUNCATE may help you, it would probably be a poor choice if you do not want to get rid of every row in your table. Since you specify a where clause, TRUNCATE is likely not what you are looking for.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

karthik M One Orange Chip Points: 29139 More actions · Answer 5

Mark,

you are right. But OP mentioned like

Query to delete, all the records from a table

Thats why i preferred TRUNCATE.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 6

Andras,

t would probably be a poor choice if you do not want to get rid of every row in your table.

You mean we need to use TRUNCATE if we dont want the log for those deleted records.

Am i correct ?

Since you specify a where clause, TRUNCATE is likely not what you are looking for.

Yes, He has to use DELETE.

karthik

Tom257 SSChampion Points: 11543 More actions · Answer 7

On first reading I thought your delete was taking over half an hour - so TRUNCATE may be an answer instead of DELETE * for a particularly large table.

However I'm guessing you really mean you want to delete records older than half an hour, in which case you'll need something like this

DELETE FROM table

WHERE dateColumn < DATEADD(mi, -30, GetDate())

you will need a suitable dateColumn containing the records creation time

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 8

karthikeyan (8/20/2008)
Andras,
t would probably be a poor choice if you do not want to get rid of every row in your table.
You mean we need to use TRUNCATE if we dont want the log for those deleted records.
Am i correct ?
...

Hi Karthik,

Basically yes, when you can use TRUNCATE, it is a good way to empty the whole table, and the individual row deletes will not be in the transaction log, so it will be fast. But TRUNCATE deletes all the rows, and this is the reason it may be less applicable to the OP (assuming I understood the question right).

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Amit Khanna-455738 SSC Eights! Points: 865 More actions · Answer 9

Hi kumar99ms ,

Can you please elaborate your question clearly?

Thanks,

Amit Khanna

adhiman SSC Veteran Points: 292 More actions · Answer 10

If you want to do a quick one then

Create another table with same structure and insert the rows that you don't want to delete into the other table.

Now truncate the original table .Now the second table has all the rows you want. Now switch the names.

This way you won't block others because you are not performing any big transaction.

I have an article that is worth trying if you want to perform deletes efficiently.

Let me know if you have questions

http://tsqltips.blogspot.com/2012/06/deleting-records-from-sql-table.html

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 11

adhiman (6/8/2012)

If you want to do a quick one then

Create another table with same structure and insert the rows that you don't want to delete into the other table.

Now truncate the original table .Now the second table has all the rows you want. Now switch the names.

This way you won't block others because you are not performing any big transaction.

I have an article that is worth trying if you want to perform deletes efficiently.

Let me know if you have questions

http://tsqltips.blogspot.com/2012/06/deleting-records-from-sql-table.html%5B/quote%5D

This isn't necessarily correct. DELETE does not necessarily (and frequently doesn't) block anything.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 12

karthikeyan-444867 (8/20/2008)
Mark,
you are right. But OP mentioned like
Query to delete, all the records from a table
Thats why i preferred TRUNCATE.

But that's only a part of what the OP wants. You cut off the other very important part that makes TRUNCATE the wrong choice in this case...

Query to delete, all the records from a table [font="Arial Black"]before half an hour[/font],…

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)