Copy field from another row with a similar ID plus prefix

I have a SQL table that contains entries such as this:

ID   | LoginCode

1    | 
1011 | abcdefg
2    | 
1012 | wxyz
44   | qwerty

I need to copy the logincode from the row with the 101 prefix (eg. 1011, 1012) to the corresponding tow without the prefix (1011 -> 1, 1012 -> 2) while ignoring the tows that have a logincode, but not corresponding ID with a prefix (there is no 10144 entry).

I am able to manually update each line using this query:

UPDATE n
SET n.logincode = p.logincode
FROM Clerk n INNER JOIN Clerk P
ON n.id = 1 AND p.id = 1011

But there are about 45 ID numbers (total about 90 entries) and I would like to use one query to update them all.

I need to eventually delete the duplicate, prefixed ID rows, and end up with something like this:

ID   | LoginCode

1    | abcdefg
2    | wxyz
44   | qwerty

Answer

update Clerk
set LoginCode = coalesce(
    (
        select p.LoginCode from Clerk as p
        where p.ID = cast('101' + cast(Clerk.ID as varchar(10)) as int)
    ),
    LoginCode
)
where LoginCode = ''; -- or is null?

If you prefer to use your inner join approach, you’d just need to change the join condition:

on p.ID = cast('101' + cast(n.ID as varchar(10)) as int)

The where clause isn’t strictly necessary. While the coalesce() will prevent you from losing values where no prefix match is found, it’s probably wise to restrict your updates to the fewest rows possible.

https://rextester.com/YXF26598