December 31, 2012 at 10:35 pm
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)
December 31, 2012 at 10:44 pm
From which table do you want to delete the records?
December 31, 2012 at 10:53 pm
I want delete table on BC_ShiftSummaryInfo
January 1, 2013 at 2:31 am
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.
January 1, 2013 at 5:24 am
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
January 1, 2013 at 11:12 pm
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;-)
January 1, 2013 at 11:51 pm
Thanks Mr.opc.three , It was working fine...with Merge delete statement
January 2, 2013 at 2:14 am
wouldn't it delete all records, mean will it keep one copy of duplicate records?
January 2, 2013 at 2:30 am
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.
January 2, 2013 at 7:12 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply