SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


delete records


delete records

Author
Message
kumar99ms
kumar99ms
Mr or Mrs. 500
Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)Mr or Mrs. 500 (536 reputation)

Group: General Forum Members
Points: 536 Visits: 550
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,
karthik M
karthik M
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11903 Visits: 2588
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
Andras Belokosztolszki
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9481 Visits: 1585
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
SuperDBA-207096
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3981 Visits: 711
truncate won't work if you are only trying to delete some of the records... it will empty the table completely.
Andras Belokosztolszki
Andras Belokosztolszki
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9481 Visits: 1585
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
karthik M
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11903 Visits: 2588
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
karthik M
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11903 Visits: 2588
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
Tom Brown
Tom  Brown
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5380 Visits: 1494
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
Andras Belokosztolszki
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9481 Visits: 1585
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
Amit Khanna-455738
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 273
Hi kumar99ms ,

Can you please elaborate your question clearly?

Thanks,
Amit Khanna
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search