Is there some reason you can't or won't use a MERGE for this?

CREATE TABLE #Weights

(id INT, shipweight1 DECIMAL(5,2)

,shipweight2 DECIMAL(5,2), shipweight3 DECIMAL(5,2))

INSERT INTO #Weights (id, shipweight1, shipweight2)

SELECT 1, 1.1, NULL

UNION ALL SELECT 2, 3.1, 2.3

SELECT * FROM #Weights

;WITH SampleData (id, [weight]) AS (

SELECT 1, 4.4

UNION ALL SELECT 2, 5.5

UNION ALL SELECT 3, 6.6)

MERGE #Weights t

USING SampleData s

ON t.id = s.id

WHEN MATCHED THEN

UPDATE SET shipweight3 = CASE WHEN shipweight2 IS NULL THEN NULL ELSE weight END

,shipweight2 = CASE WHEN shipweight2 IS NULL THEN [weight] ELSE shipweight2 END

WHEN NOT MATCHED THEN

INSERT (id, shipweight1)

VALUES (s.id, weight);

SELECT * FROM #Weights

DROP TABLE #Weights

Looks a bit cleaner to me.