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

Query for delete records between date range? Expand / Collapse
Author
Message
Posted Monday, December 31, 2012 10:35 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:12 AM
Points: 1,015, Visits: 2,841
Hi,

Need Query for delete records between date range, I want delete --(3377 row(s) as below two tables


Select A.SS_ID,A.SD_ID, A.SD_Info, A.SD_User, A.SD_Date 
from dbo.BC_ShiftSummaryInfo A
Join dbo.BC_ShiftSummary B
On A.SS_ID = B.SS_ID
Where B.ShiftStartDate between '2012-12-14 06:00:00.000'
and '2012-12-14 22:00:00.000' order by B.ShiftStartDate
--(3377 row(s) affected)

Post #1401550
Posted Monday, December 31, 2012 10:44 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:49 AM
Points: 22,514, Visits: 30,244
From which table do you want to delete the records?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1401551
Posted Monday, December 31, 2012 10:53 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:12 AM
Points: 1,015, Visits: 2,841
I want delete table on BC_ShiftSummaryInfo

Post #1401553
Posted Tuesday, January 01, 2013 2:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:49 AM
Points: 187, Visits: 699
you just need to replace the select-command with an delete-command, followed by the table to delete the data from. Then remove the order-by-clause and that's it.

delete from dbo.BC_ShiftSummaryInfo
from dbo.BC_ShiftSummaryInfo A
Join dbo.BC_ShiftSummary B
On A.SS_ID = B.SS_ID
Where B.ShiftStartDate between '20121214 06:00:00.000'
and '20121214 22:00:00.000'

If ure are still not sure use a BEGIN TRANSACTION at the beginning and a ROLLBACK transaction at the end of the command. This way you get the number of deleted records without actually deleting the records.
When using the OUTPUT-command you can even see which records would have been deleted:
BEGIN TRANSACTION
delete from dbo.BC_ShiftSummaryInfo
output deleted.*
from dbo.BC_ShiftSummaryInfo A
Join dbo.BC_ShiftSummary B
On A.SS_ID = B.SS_ID
Where B.ShiftStartDate between '20121214 06:00:00.000'
and '20121214 22:00:00.000'
ROLLBACK TRANSACTION

When sure the command is doing what you want just replace the ROLLBACK with a COMMIT and the delete operation is finally executed.



Something off topic:
As you may have seen I removed the "-" from the datetime-strings. You should do that too in future scripts. When using the format "YYYY-MM-DD" the date can be interpreted in the us-english oder british-english(or german) format, switching the day and the month. It's enough to have different default language set for a user or a connection.
So always use the format "YYYYMMDD". It is always interpreted the same way independent of the language settings.
Post #1401565
Posted Tuesday, January 01, 2013 5:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
A DELETE-JOIN will work well. One other option I thought I would share. After working with it more and more I find the MERGE syntax to be quite intuitive and a little easier to use when debugging (my opinion). With the query written this way I can highlight the SELECT statement in the USING clause to see what would be deleted without changing the query or carrying additional debug-comments in the code:

BEGIN TRAN;

MERGE dbo.BC_ShiftSummaryInfo AS target
USING
(
SELECT A.SS_ID
FROM dbo.BC_ShiftSummaryInfo A
JOIN dbo.BC_ShiftSummary B ON A.SS_ID = B.SS_ID
WHERE B.ShiftStartDate BETWEEN '2012-12-14 06:00:00.000'
AND '2012-12-14 22:00:00.000'
) AS source (SS_ID)
ON source.SS_ID = target.SS_ID
WHEN MATCHED
THEN DELETE;

ROLLBACK;

OUTPUT is also supported with MERGE:

BEGIN TRAN;

MERGE dbo.BC_ShiftSummaryInfo AS target
USING
(
SELECT A.SS_ID
FROM dbo.BC_ShiftSummaryInfo A
JOIN dbo.BC_ShiftSummary B ON A.SS_ID = B.SS_ID
WHERE B.ShiftStartDate BETWEEN '2012-12-14 06:00:00.000'
AND '2012-12-14 22:00:00.000' ) AS source (SS_ID)
ON source.SS_ID = target.SS_ID
WHEN MATCHED
THEN DELETE
OUTPUT
$ACTION,
DELETED.*;

ROLLBACK;



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1401581
Posted Tuesday, January 01, 2013 11:12 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
opc.three (1/1/2013)
With the query written this way I can highlight the SELECT statement in the USING clause to see what would be deleted without changing the query or carrying additional debug-comments in the code:
thanks for this awesome approach , can you please help with the SELECT (you mentioned in quote) query for below query ?

BEGIN TRAN;

MERGE dbo.BC_ShiftSummaryInfo AS target
USING
(
SELECT A.SS_ID
FROM dbo.BC_ShiftSummaryInfo A
JOIN dbo.BC_ShiftSummary B ON A.SS_ID = B.SS_ID
WHERE B.ShiftStartDate BETWEEN '2012-12-14 06:00:00.000'
AND '2012-12-14 22:00:00.000'
) AS source (SS_ID)
ON source.SS_ID = target.SS_ID
WHEN MATCHED
THEN DELETE;

ROLLBACK;



-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1401659
Posted Tuesday, January 01, 2013 11:51 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:12 AM
Points: 1,015, Visits: 2,841
Thanks Mr.opc.three , It was working fine...with Merge delete statement
Post #1401670
Posted Wednesday, January 02, 2013 2:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 2:12 AM
Points: 1, Visits: 22
wouldn't it delete all records, mean will it keep one copy of duplicate records?
Post #1401702
Posted Wednesday, January 02, 2013 2:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:12 AM
Points: 1,015, Visits: 2,841
deleted all records BETWEEN '2012-12-14 00:00:00.000' AND '2012-12-15 00:00:00.000', specifytime period...Not for whole table data.
Post #1401707
Posted Wednesday, January 02, 2013 7:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
Bhuvnesh (1/1/2013)
opc.three (1/1/2013)
With the query written this way I can highlight the SELECT statement in the USING clause to see what would be deleted without changing the query or carrying additional debug-comments in the code:
thanks for this awesome approach , can you please help with the SELECT (you mentioned in quote) query for below query ?

BEGIN TRAN;

MERGE dbo.BC_ShiftSummaryInfo AS target
USING
(
SELECT A.SS_ID
FROM dbo.BC_ShiftSummaryInfo A
JOIN dbo.BC_ShiftSummary B ON A.SS_ID = B.SS_ID
WHERE B.ShiftStartDate BETWEEN '2012-12-14 06:00:00.000'
AND '2012-12-14 22:00:00.000'
) AS source (SS_ID)
ON source.SS_ID = target.SS_ID
WHEN MATCHED
THEN DELETE;

ROLLBACK;


What did you want to know? If you were thinking this MERGE would de-duplicate the range, it won't. I was just showing an alternate to using DELETE-JOIN that might be a little more expressive.

Only deleting duplicatesedit, added within the range of ShiftStartDate specified requires additional information. We started to go down this road over here but maybe it is not required.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1401826
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse