July 27, 2015 at 5:39 pm
Hi, can I safely use top n select/delete in a while loop? For example:
declare @FieldVal int
while (select count(*) from @MyTempTable) > 0
begin
select top 1 @FieldVal = FieldVal from @MyTempTable
-- process @FieldVal then delete the row
delete top 1 from @MyTempTable
end
I like the simplicity of the above approach as long as it's reliable and there aren't any gotchas that I may not be aware of.
July 27, 2015 at 5:43 pm
sqlguy-736318 (7/27/2015)
Hi, can I safely use top n select/delete in a while loop? For example:declare @FieldVal int
while (select count(*) from @MyTempTable) > 0
begin
select top 1 @FieldVal = FieldVal from @MyTempTable
-- process @FieldVal then delete the row
delete top 1 from @MyTempTable
end
I like the simplicity of the above approach as long as it's reliable and there aren't any gotchas that I may not be aware of.
No, it is not safe. This is because neither the SELECT nor the DELETE have an ORDER BY clause on a unique column set... so it's not defined which row will be either selected or deleted. I'd suggest adding an identity column to the table variable, selecting that out into a variable, and then deleting based upon that.
However, loops would be slow. I'd recommend trying to make this more set-based for performance reasons. There are people here that could help you out with that. Just follow the instructions in the first link in my signature for how to post your stuff so that people will want to help you.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 28, 2015 at 11:36 am
My question is more specific: For a given result set in a temp table, will "select top 1" and "delete top 1" operate on the same row in a single iteration of a while loop? For the purposes of this temp table, it doesn't matter which row in the result set is picked by "top 1" for select/delete. For example, it doesn't matter if it's the first row, third row, nth row in the result set. As long as the row picked for selection is the same for select as it is for delete. So do you know what SS behavior is for this scenario?
July 28, 2015 at 11:44 am
Chances are in your case yes they would operate on the same row but that's by no means guaranteed unless you specify more criteria and definitely not something you would want to do on a production system if you needed to operate on the same row. As was suggested grab some fields from the temp table that will make it unique and use those as criteria for the delete.
July 28, 2015 at 11:47 am
sqlguy-736318 (7/28/2015)
My question is more specific: For a given result set in a temp table, will "select top 1" and "delete top 1" operate on the same row in a single iteration of a while loop? For the purposes of this temp table, it doesn't matter which row in the result set is picked by "top 1" for select/delete. For example, it doesn't matter if it's the first row, third row, nth row in the result set. As long as the row picked for selection is the same for select as it is for delete. So do you know what SS behavior is for this scenario?
i would say no, there's no guarantee, since there's no WHERE statement to identify which row to delete. also DELETE TOP 1 isn't a valid syntax, right?
maybe it's a copy paste error, but i don't see
WHERE FieldVal = @FieldVal
this seems to be a better example, if you are going to use a loop to delete
DECLARE @MyTempTable TABLE(FieldVal INT,FieldName VARCHAR(128) );
INSERT INTO @MyTempTable
SELECT TOP 5 OBJECT_ID AS FieldVal,name AS FieldName FROM sys.tables;
DECLARE @FieldVal INT;
WHILE (SELECT Count(*)
FROM @MyTempTable) > 0
BEGIN
SELECT TOP 1 @FieldVal = FieldVal
FROM @MyTempTable;
-- process @FieldVal then delete the row
DELETE FROM @MyTempTable
WHERE FieldVal = @FieldVal;
END;
Edit: 2014 wanted parenthesis around the value: DELETE TOP (1) FROM @MyTempTable
WHERE FieldVal = @FieldVal;
Lowell
July 28, 2015 at 11:48 am
sqlguy-736318 (7/27/2015)
Hi, can I safely use top n select/delete in a while loop? For example:declare @FieldVal int
while (select count(*) from @MyTempTable) > 0
begin
select top 1 @FieldVal = FieldVal from @MyTempTable
-- process @FieldVal then delete the row
delete top 1 from @MyTempTable
end
I like the simplicity of the above approach as long as it's reliable and there aren't any gotchas that I may not be aware of.
I think you should change your delete statement to:
delete from @MyTempTable where FieldVal = @FieldVal
Otherwise, you could end up deleting records for values of FieldVal you have not processed yet.
To answer your question, I don't think your logic was safe.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 28, 2015 at 11:49 am
sqlguy-736318 (7/28/2015)
My question is more specific: For a given result set in a temp table, will "select top 1" and "delete top 1" operate on the same row in a single iteration of a while loop? For the purposes of this temp table, it doesn't matter which row in the result set is picked by "top 1" for select/delete. For example, it doesn't matter if it's the first row, third row, nth row in the result set. As long as the row picked for selection is the same for select as it is for delete. So do you know what SS behavior is for this scenario?
I find it impossible to believe that which row you delete doesn't matter. If you are attempting to process all the rows, and you delete one you haven't processed yet, you can be quite sure it will matter. Also, if you have two separate queries, one a SELECT, and another, a DELETE, and think that TOP (1) is going to be guaranteed to choose the same row in both queries, then you're out of luck, because without a sufficently specific ORDER BY clause, which row is picked is a random event for each query. After all, without an ORDER BY clause, how is SQL Server supposed to know which row to pick? Remember, in SQL Server, a table is not guaranteed to be in ANY particular order unless you specify one. That behavior is by design, and you really wouldn't want it any other way.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 28, 2015 at 12:39 pm
sqlguy-736318 (7/27/2015)
Hi, can I safely use top n select/delete in a while loop? For example:declare @FieldVal int
while (select count(*) from @MyTempTable) > 0
begin
select top 1 @FieldVal = FieldVal from @MyTempTable
-- process @FieldVal then delete the row
delete top 1 from @MyTempTable
end
I like the simplicity of the above approach as long as it's reliable and there aren't any gotchas that I may not be aware of.
My simple question: Why are you processing one row at a time?
July 28, 2015 at 1:23 pm
ok I'll include an id in the result set and go through the while loop using select max(id).
July 28, 2015 at 3:11 pm
sqlguy-736318 (7/28/2015)
ok I'll include an id in the result set and go through the while loop using select max(id).
How about answering the question as to why you have to process your data in a row by row manner. A set based process would probably be more scalable unless there is a valid reason to do it row by row.
July 28, 2015 at 3:21 pm
it's a one-off report with 2 levels of nested foreach logic
July 28, 2015 at 3:37 pm
Okay, obviously you are set on doing this using a loop instead of taking an opportunity to see if there may be a better way to accomplish the task.
July 29, 2015 at 10:04 am
Lynn Pettis (7/28/2015)
Okay, obviously you are set on doing this using a loop instead of taking an opportunity to see if there may be a better way to accomplish the task.
Its probably the case that "-- process @FieldVal then delete the row" implies that theres a process that expects a single key to operate on. I can see his point, especially since he said its a "one off", and sometimes its difficult and time consuming to convert whatever that "process @fieldval" entails to a set oriented procedure, and it may be worth the time to just run it like he intends to given his post.
Especially if that "process" means calling a stored procedure, and calling stored procedures in a set oriented manner doesn't look like a T-SQL thing (as much as I'd like it to be!)
July 29, 2015 at 10:30 am
Alvin Ramard (7/28/2015)
sqlguy-736318 (7/27/2015)
Hi, can I safely use top n select/delete in a while loop? For example:declare @FieldVal int
while (select count(*) from @MyTempTable) > 0
begin
select top 1 @FieldVal = FieldVal from @MyTempTable
-- process @FieldVal then delete the row
delete top 1 from @MyTempTable
end
I like the simplicity of the above approach as long as it's reliable and there aren't any gotchas that I may not be aware of.
I think you should change your delete statement to:
delete from @MyTempTable where FieldVal = @FieldValOtherwise, you could end up deleting records for values of FieldVal you have not processed yet.
To answer your question, I don't think your logic was safe.
unless FieldVal is a key of some kind, neither is this version of the delete. This will kill however many records match that particular value.
Even the ORDER BY can be dangerous if you don't make sure that it's a bullet-proof, unambiguous order scheme.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply