How can I delete data from a field every 24 hours to the second based on a bit field?
-
Fellow DBA's,
How can I delete data from a field every 24 hours to the second based on a bit field? The process should delete data from a field based on a bit field showing complete. Please enlighten me!
Thanks!
-
You need to define what you mean. If you mean for example that you want to delete every single record at midnight that was marked complete until 11:59:59, then you need to have a datetime field to accompany the bit field. There's a 99% chance on most systems that's all it will delete anyway, but we have systems here that are still doing 100-150 t/sec so it would by no means be a guarantee. So, add a datestamp column.
If this isn't what you are talking about, then give table structure, a few rows of sample data, and a result set explaining what you are trying to achieve.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA. -
Mean Old DBA,
I'm trying to delete data from the "Response" column based on whether the "Completed" column has a status of '1'. It should only delete the "Response" column data if the "DateIn" data is older than 48 hours. This can run as a stored procedure in a job, DTS, or whatever gets it done, and can be scheduled every night.
Table: TransactionRequests
Columns: DateIn (datetime, null), Response (ntext, null), Completed (bit, not null)
Data (same order as columns above): "2004-04-29 10:39:00.883"
"Me2jogzwE5j+xbs9M4urEu51gjIayxD8j0in4tMTuli8"
"1"
Hope this helps,
DBA Dave
-
Try this
Delete from TransactionRequests
Where DateDiff(hh,getdate(),datein)>48
AND Completed = 1
-
That worked! I actually changed the statement to update the response field only and empty out the data.
Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply