June 12, 2014 at 12:57 pm
delete table
from table t join
(select col1,col2,col3,col4, from table
where convert(varchar,insertdate,112) < convert(varchar,getdate(),112)
) y
on t.col1 =y.col1
and t.col2=y.col2
and t.col3=y.col3
and t.col4=y.col4
where convert(varchar,t.insertdate,112)=convert(varchar,getdate(),112)
June 12, 2014 at 12:59 pm
It deletes rows with the current date/day if the table contains any row with a date before today's date where the 4 column values match today's 4 column values.
Edit: btw, the date checks are improperly coded, and should be like this instead:
where insertdate < convert(varchar,getdate(),112)
...
where insertdate >= convert(varchar,getdate(),112) and
insertdate < convert(varchar,getdate() + 1,112)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 12, 2014 at 1:01 pm
Does it leave today's data in there or delete those as well?
June 12, 2014 at 1:55 pm
It deletes only today's date, but only if there are matching records before today.
Apparently, based on the code, only the earliest matching row, across the 4 columns compared, is needed in the table. To me, it looks like the code is designed to run (at least) daily.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 12, 2014 at 2:35 pm
how can we modify this to keep today's record but delete previous entries?
June 12, 2014 at 2:39 pm
The quick way would be switch the "=" and "<" in the current code 😉 :
delete table
from table t join
(select col1,col2,col3,col4, from table
where convert(varchar,insertdate,112) = convert(varchar,getdate(),112)
) y
on t.col1 =y.col1
and t.col2=y.col2
and t.col3=y.col3
and t.col4=y.col4
where convert(varchar,t.insertdate,112) < convert(varchar,getdate(),112)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 12, 2014 at 3:38 pm
SQL_Surfer (6/12/2014)
how can we modify this to keep today's record but delete previous entries?
Have you tried MERGE to avoid doing this in 2 steps?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply