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


Query for delete records between date range?


Query for delete records between date range?

Author
Message
SQL Galaxy
SQL Galaxy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 3465
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)


Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37973
From which table do you want to delete the records?

Cool
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)
SQL Galaxy
SQL Galaxy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 3465
I want delete table on BC_ShiftSummaryInfo
WolfgangE
WolfgangE
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 777
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.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8243 Visits: 14368
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
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2928 Visits: 4076
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;-)
SQL Galaxy
SQL Galaxy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 3465
Thanks Mr.opc.three , It was working fine...with Merge delete statement
alpana_deshpande
alpana_deshpande
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 22
wouldn't it delete all records, mean will it keep one copy of duplicate records?
SQL Galaxy
SQL Galaxy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 3465
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.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8243 Visits: 14368
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
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