SET .. WHERE .. within batch throwing an error in SSMS v18.4

I am revisiting MS SQL after a few years, so apologies for a basic question!

I am performing some address data cleansing, to expand abbreviations like DR into ‘DRIVE’ RD into ‘ROAD’ etc to help with identifying duplicate addresses.

I am exploring ways to optimise my query by taking my existing block of 10 sequential SET .. WHERE .. statements into a batch or some other method, as I am working on approximately 500k records.

When I converted my block of existing SET .. WHERE .. statements into a simple batch, this is throwing an “incorrect syntax near the keyword where” error. Does anyone have any ideas or suggestions to resolve this? Here is what the code looks like (with a cut down list of SET .. WHERE .. statements):

declare @batchSize int = 10000;
declare @rows int = -1;

while @rows <> 0
begin
 raiserror('Beginning loop. Last ROWCOUNT was %d',0,1, @rows) with nowait;
 update top (@batchSize) DBO.POSTAL_ADDRESS_MATCH_1
 SET ADDRESS1=REPLACE(ADDRESS1,' DR',' DRIVE') WHERE RIGHT(ADDRESS1,3) LIKE ' DR'
 -- 9 other SET WHERE statements go here
 where ID between @startID and @startID + @batchSize;

 set @rows = @@ROWCOUNT;
 set @startID += @batchSize;
end;

Many thanks in advance for your help.

Answer

That’s not the correct syntax for the SET clause of an UPDATE statement. You’re looking for CASE.

 UPDATE TOP (@batchSize) DBO.POSTAL_ADDRESS_MATCH_1
 SET ADDRESS1 = 
   CASE 
     WHEN RIGHT(ADDRESS1,3) LIKE ' DR' THEN REPLACE(ADDRESS1,' DR',' DRIVE') 
     WHEN RIGHT(ADDRESS1,3) LIKE ' ST' THEN REPLACE(ADDRESS1,' ST',' STREET') 
     ...etc...
     ELSE ADDRESS1 --<---Leave it alone if there's not a matching pattern.
     END
 where ID between @startID and @startID + @batchSize;