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
Thursday, March 1, 2007
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment