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

1 comment:

aiya said...

Office 2010is powerful!
Microsoft Office 2010is the best software in the world.
Microsoft wordis so great!
Office 2007makes life great!
Many people likeMicrosoft Office.
Microsoft Office 2007is welcomed by the whole world.
Office 2007 keyis available here.
Office 2007 downloadis on sale now!
Office 2007 Professionalbring me so much convenience.
Outlook 2010 make life wonderful!
Microsoft outlook is my love.
Microsoft outlook 2010 is convenient!
Windows 7 is convenient!