--nb20211207 Update link using delphi color
--begin tran
--SELECT * FROM dbo.Waregroups (nolock) 

;WITH ColorCTE (ColorId, DelphiColorValue) AS (
select Max(ColorId), ((256 * 256 * Blue) + (256 * Green) + Red) 
from dbo.Color C (nolock) 
  join dbo.ColorRGB RGB (nolock) on C.FK_ColorRGBID = RGB.ColorRGBID
WHERE Waregroup = 1
GROUP BY ((256 * 256 * Blue) + (256 * Green) + Red)
)
UPDATE dbo.Waregroups SET FkColor = C.ColorId
FROM ColorCTE C (nolock) 
  join dbo.Waregroups WG (nolock) on C.DelphiColorValue = WG.Color

--SELECT * FROM dbo.Waregroups (nolock) 
--rollback tran