I have a SQL table with Postal Codes like this:

And another table, like this:

I need a query that updates the second table with the nearest postal code (from the first table). The result should be:

CodePudding user response：

Try the following:

with cte as(
Select A.Postal_Code As UpdateTo,B.Postal_Code As ToBeUpdated, 
Row_Number() Over (Partition By B.Postal_Code Order By Abs(A.Postal_Code-B.Postal_Code)) as rn
From Postals A , Clients B 
)

Update Clients C Set C.Postal_Code = (Select D.UpdateTo From cte D 
                                      where C.Postal_Code=D.ToBeUpdated and D.rn=1);
select * from Clients

See a demo from db-fiddle.

CodePudding user response：

update Table2
set PostalCode = (
    select top 1 t1.PostalCode
    from Table1 t1
    /* optionally improve the lookup by requiring first two digits to match */
    where t1.PostalCode like left(Table2.PostalCode, 2)   '%'
    order by
        abs(cast(t1.PostalCode as int) - cast(Table2.PostalCode as int)),
        t1.PostalCode /* ties go low */
)
where not exists (
    select 1 from Table1 t1 where t1.PostalCode = Table2.PostalCode
)

This is generic SQL with these requirements: There's an absolute value function, that string concatenation is (try || if not), that you have top 1 or limit 1 available, that postal codes are stored as strings that easily convert to integer.

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a7e0383ff6c23d7fc33070066a4abaa3

Here's a version that might be appropriate for SQL Server specifically. It does separate searches for next lower and next higher values. Depending on the indexing it could be the more efficient approach.

/* more complicated but possibly a better plan */
update c
set PostalCode = case when abs(Code - Code1) <= abs(Code - Code2)
                      then Code1 else coalesce(Code2, Code1) end /* ties go low */
from Clients c
    cross apply (values (cast(PostalCode as int))) z0(Code) cross apply (
    select min(PostalCode), cast(min(PostalCode) as int) from Zips z
    where z.PostalCode > c.PostalCode
) z1(PostalCode1, Code1) cross apply (
    select max(PostalCode), cast(min(PostalCode) as int) from Zips z
    where z.PostalCode < c.PostalCode
) z2(PostalCode2, Code2)
where not exists (
    select 1 from Zips z where z.PostalCode = c.PostalCode
);