Thursday, March 1, 2007

UPDATE Nulls

Recently I got a 'specific' request from one of our customers to update a column (which allows NULLs).Let see the following script

CREATE TABLE test (col1 INT NOT NULL PRIMARY KEY,col2 DECIMAL(5,2))
GO
INSERT INTO test (col1, col2) VALUES (1, 3.5)
INSERT INTO test (col1, col2) VALUES (2, null)
INSERT INTO test (col1, col2) VALUES (3, null)
INSERT INTO test (col1, col2) VALUES (4, null)
INSERT INTO test (col1, col2) VALUES (5, null)
INSERT INTO test (col1, col2) VALUES (6, 7.4)
INSERT INTO test (col1, col2) VALUES (7, null)
INSERT INTO test (col1, col2) VALUES (8, null)
INSERT INTO test (col1, col2) VALUES (9, null)
INSERT INTO test (col1, col2) VALUES (10, 2.9)
INSERT INTO test (col1, col2) VALUES (11, null)
INSERT INTO test (col1, col2) VALUES (12, null)

So we would like to update NULL's with value till first non-null value
UPDATE test
SET col2 = (SELECT r.col2
FROM test AS r
WHERE r.col2 IS NOT NULL
AND r.col1 = (SELECT MAX(rr.col1)
FROM test AS rr WHERE
rr.col2 IS NOT NULL AND rr.col1 < test.col1))
WHERE col2 IS NULL


SELECT * FROM test

No comments: