CODEWITHSUNDEEP
sqlt-sql
Bridget Midget
Bridget Midget

Reputation: 79

SQL: Updating row within loop

I'm trying to remove the first character in LastNames that start with a space. The code below seems to clear LastName entirely, making the field empty. Do you see something wrong?

DECLARE @Id int
DECLARE @LastName NVARCHAR(255)

while (SELECT COUNT(*) FROM [Table] WHERE LastName LIKE ' %') > 0
Begin

    Select Top 1 @Id = Id, @LastName = LastName FROM [Table] WHERE LastName LIKE ' %'
    UPDATE [Table] SET LastName = SUBSTRING(@LastName, 1, LEN(@LastName)) WHERE Id = @Id
End

Upvotes: 1

Views: 1070

Answers (2)

Frederik Gheysels
Frederik Gheysels

Reputation: 56954

I don't quite understand why you're using a LIKE '°%' statement in the SELECT statement, while restricting. Next to that, you should avoid executing queries inside a loop, since that will hurt performance badly.

SQL is set based , so it is perfectly possible to execute this code without using a loop.

UPDATE [Table]
SET LastName = LTRIM(LastName)
WHERE LastName LIKE ' %'

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453648

For me the code you posted runs in an infinite loop.

LastName = SUBSTRING(LastName, 1, LEN(LastName)) will have no effect other than removing right trailing space (as that is not counted by LEN). To remove the left most character you would need SUBSTRING(LastName, 2, LEN(LastName) - 1).

But, you don't need a RBAR (Row By Agonizing Row) loop here just use

UPDATE [Table]
SET LastName = LTRIM(LastName) 
WHERE LastName  LIKE ' %'

to update all matching rows in a set based way.

Upvotes: 3

Related Questions