Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


delete records


delete records

Author
Message
kumar99ms
kumar99ms
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 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
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2577 Visits: 2582
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
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1929 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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1747 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
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1929 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
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2577 Visits: 2582
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
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2577 Visits: 2582
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1625 Visits: 1469
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
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1929 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
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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