[Solved] Updating a column based on values from other rows [closed]

by


Following your edit…

DECLARE @T TABLE
(
ID INT,
CategoryID CHAR(4),
Code CHAR(4),
Status CHAR(4) NULL
)
INSERT INTO @T (ID,CategoryID, Code)
SELECT 1,'A100',0012 UNION ALL SELECT 2,'A100',0012 UNION ALL
SELECT 3,'A100',0055 UNION ALL SELECT 4,'A100',0012 UNION ALL
SELECT 5,'B201',1116 UNION ALL SELECT 6,'B201',1116 UNION ALL
SELECT 7,'B201',1121 UNION ALL SELECT 8,'B201',1024;

WITH T AS
(
SELECT *, MIN(Code) OVER (PARTITION BY CategoryID ) AS MinCode
from @T
)
UPDATE T
SET Status="FAIL"
WHERE Code <> MinCode

SELECT *
FROM @T

Returns

ID          CategoryID Code Status
----------- ---------- ---- ------
1           A100       12   NULL
2           A100       12   NULL
3           A100       55   FAIL
4           A100       12   NULL
5           B201       1116 FAIL
6           B201       1116 FAIL
7           B201       1121 FAIL
8           B201       1024 NULL

solved Updating a column based on values from other rows [closed]