if there is a 1 in field1 i need to treat it as though its a null so i tried this but no luck. can you see what i am missing please.thanks

declare @thedatalookslikethis table (id int, field1 varchar(10), field2 varchar(10), field3 varchar(10), field4 varchar(10))

insert @thedatalookslikethis

select 1, 'a', null, 'b', null

union select 2, null, '9', null, '6'

union select 3, '2', null, null, '8'

union select 4, 'a', null, null, null

union select 5, '1', 'a', null, null

union select 6, null, null, 'a', null

union select 7, null, null, null, 'a'

union select 8, null, null, 'a', 'b'

union select 9, 'a', null, 'b', 'c'

union select 10, 'a', 'b', null, 'c'

union select 11, null, 'a', 'b', 'c'

;WITH shiftedFields

AS

(

SELECT

t.id,

COALESCE(t.field1, t.field2, t.field3, t.field4) AS field1_new,

CASE WHEN (ISNULL(t.field1, '1') != '1') THEN COALESCE(t.field2, t.field3, t.field4)

WHEN t.field2 IS NOT NULL THEN COALESCE(t.field3, t.field4)

WHEN t.field3 IS NOT NULL THEN t.field4

ELSE NULL END AS field2_new,

CASE WHEN ((ISNULL(t.field1, '1') != '1') AND t.field2 IS NOT NULL) THEN COALESCE(t.field3, t.field4)

WHEN ((ISNULL(t.field1, '1') != '1') AND (t.field3 IS NOT NULL OR

t.field2 IS NOT NULL AND t.field3 IS NOT NULL)) THEN t.field4

ELSE NULL END AS field3_new,

CASE WHEN (ISNULL(t.field1, '1') != '1') AND (t.field2 IS NOT NULL AND t.field3 IS NOT NULL) THEN t.field4

ELSE NULL END AS field4_new

FROM @thedatalookslikethis t

)

UPDATE t

SET Field1 = sf.field1_new

,Field2 = sf.Field2_new

,Field3 = sf.Field3_new

,Field4 = sf.Field4_new

FROM @thedatalookslikethis t

JOIN shiftedFields sf ON sf.id = t.id

SELECT * FROM @thedatalookslikethis