Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

delete records Expand / Collapse
Author
Message
Posted Wednesday, August 20, 2008 4:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 25, 2009 4:57 PM
Points: 74, 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,
Post #555650
Posted Wednesday, August 20, 2008 4:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 2,025, Visits: 2,521
TRUNCATE may help for your need.

before half an hour,…


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


karthik
Post #555657
Posted Wednesday, August 20, 2008 4:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
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
Post #555659
Posted Wednesday, August 20, 2008 4:58 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711
truncate won't work if you are only trying to delete some of the records... it will empty the table completely.
Post #555660
Posted Wednesday, August 20, 2008 5:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
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
Post #555663
Posted Wednesday, August 20, 2008 5:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 2,025, Visits: 2,521
Mark,

you are right. But OP mentioned like

Query to delete, all the records from a table


Thats why i preferred TRUNCATE.


karthik
Post #555664
Posted Wednesday, August 20, 2008 5:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 2,025, Visits: 2,521
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
Post #555672
Posted Wednesday, August 20, 2008 5:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:27 PM
Points: 1,293, Visits: 1,429
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
Post #555674
Posted Wednesday, August 20, 2008 5:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
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
Post #555678
Posted Wednesday, August 20, 2008 5:35 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 23, 2014 8:26 AM
Points: 89, Visits: 230
Hi kumar99ms ,

Can you please elaborate your question clearly?

Thanks,
Amit Khanna
Post #555685
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse