UPDATE cadvehicleconversion
SET    vcolor = CASE (
                       SELECT TOP 1 CD.ReportCode
                       FROM   CombinedDesc CD
                       WHERE  CD.TypeID = 137
                              AND CD.ID = [ColorID]
                   )
                   
When 'BGE' Then 'BGE'
When 'BLK' Then 'BLK'
When 'BLU' Then 'BLU'
When 'DBL' Then 'DBL'
When 'LBL' Then 'LBL'
When 'BRZ' Then 'BRZ'
When 'BRO' Then 'BRO'
When 'COM' Then 'COM'
When 'CPR' Then 'CPR'
When 'CRM' Then 'CRM'
When 'GLD' Then 'GLD'
When 'GRY' Then 'GRY'
When 'GRN' Then 'GRN'
When 'DGR' Then 'DGR'
When 'Lgr' Then 'LGR'
When 'LAV' Then 'LAV'
When 'MAR' Then 'MAR'
When 'ONG' Then 'ONG'
When 'PNK' Then 'PNK'
When 'PLE' Then 'PLE'
When 'RED' Then 'RED'
When 'SIL' Then 'SIL'
When 'TAN' Then 'TAN'
When 'TEA' Then 'TEA'
When 'TRQ' Then 'TRQ'
When 'WHI' Then 'WHI'
When 'YEL' Then 'YEL'

                   ELSE ''
              END
GO

CREATE TABLE [dbo].[CombinedDesc](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[TypeID] [int] NULL,
	[Active] [int] NULL,
	[Description] [varchar](250) NULL,
	[ReportCode] [varchar](250) NULL,
	[Category] [varchar](250) NULL,
	[Info1] [varchar](250) NULL,
	[Info2] [varchar](250) NULL,
 CONSTRAINT [PK_CombinedDesc21_1__14] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

UPDATE CVC 
SET vcolor = CASE WHEN CD.ReportCode IN('BGE','BLK','BLU','DBL','LBL','BRZ','BRO','COM','CPR','CRM',
'GLD','GRY','GRN','DGR','LGR','LAV','MAR','ONG','PNK','PLE','RED','SIL','TAN','TEA','TRQ','WHI','YEL')
THEN CD.ReportCode ELSE '' END
FROM cadvehicleconversion CVC
INNER JOIN CombinedDesc CD ON CD.TypeID = 137 AND CD.ID = CVC.[ColorID]