Issue searching down columns

Question

In SQL server 2016 I have a table, let's say like this:

ID.	Number	details
1	02	Temp
1	03	Temp/01
2	04	Temp/04

How would I write an update script to use the last 2 digits of the details column to update all number columns where the ID is the same? Is this even possible?

I used:

Update Table
Set Number = RIGHT(details, 02)
Where number NOT LIKE CONCAT('%', details)

But obviously that didn't work so I added a:

AND details LIKE '%/%'

To sort out the details columns without a number at the end. However this isn't really a solution as it leaves columns with matching ID's but different number values

Answer 1

Reading between the lines, seems like you could use conditional (windowed) aggregation and an updatable CTE here:

USE Sandbox;
GO

SELECT *
INTO dbo.YourTable
FROM (VALUES(1,'02','Temp'),
            (1,'03','Temp/01'),
            (2,'04','Temp/04'))V([ID.],Number,Details) --ID. is a poor choice for a column name; "." characters should not be in names
GO
WITH CTE AS(
    SELECT [ID.],
           Number,
           Details,
           MAX(CASE WHEN Details LIKE '%/[0-9][0-9]' THEN RIGHT(Details,2) END) OVER (PARTITION BY [ID.]) AS Suffix
    FROM dbo.YourTable)
UPDATE CTE
SET Number = Suffix;
GO
SELECT *
FROM dbo.YourTable;
GO
DROP TABLE dbo.YourTable;