Fill NULL records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Fill NULL records

Author

Topic

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-07-08 : 11:50:56

Hi

I try to find an fairly simple algorithm to fill my tables NULL with the value that was heading last.

I show you how:

First the original table

ID | A | B | C
1 | 132 | Bd32| A1
2 | NULL| NULL| B6
3 | NULL| NULL| B7
4 | 512 | AX44| B1
5 | NULL| NULL| B8
6 | NULL| NULL| B1

And from this i want to fill it out like this

ID | A | B | C
1 | 132 | Bd32| A1
2 | 132 | Bd32| B6
3 | 132 | Bd32| B7
4 | 512 | AX44| B1
5 | 512 | AX44| B8
6 | 512 | AX44| B1

You can see only column A and B have to be filled out

How can that be achieved?

I start to make an fetch cursor, but...
cannot it be done in a single query?

Most greatful

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-08 : 13:36:37

Here is one way:

-- Sample Data
DECLARE @T TABLE (ID INT, A INT, B VARCHAR(4), C VARCHAR(2))
INSERT @T
SELECT 1 , 132 , 'Bd32', 'A1'
UNION ALL SELECT 2 , NULL, NULL, 'B6'
UNION ALL SELECT 3 , NULL, NULL, 'B7'
UNION ALL SELECT 4 , 512 , 'AX44', 'B1'
UNION ALL SELECT 5 , NULL, NULL, 'B8'
UNION ALL SELECT 6 , NULL, NULL, 'B1'

-- Run Update
UPDATE
	T
SET
	A = D.A,
	B = D.B
FROM 
	@T AS T
OUTER APPLY
(
	SELECT 
		B.*
	FROM
		(
			SELECT MAX(ID) AS ID
			FROM @T
			WHERE A IS NOT NULL
			AND ID < T.ID
		) AS A
	INNER JOIN
		@T AS B
		ON A.ID = B.ID
) AS D

-- Verify Results
SELECT *
FROM @T AS T

This assumes that both A and B with both NULL. If not you might need to run two updates or change the logic.

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-07-08 : 15:38:03

Hi Lamprey

That could work!

Thanks!

rocknpop
Posting Yak Master

201 Posts

Posted - 2010-07-09 : 00:56:57

continuing with above sample table:

DECLARE @a INT, @b VARCHAR(4)
UPDATE @t1
SET @a=A=CASE WHEN A IS NULL THEN @a ELSE A END,@b=B=CASE WHEN B IS NULL THEN @b ELSE B END

--------------------
Rock n Roll with SQL

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-07-11 : 01:44:27

@RocknPop:
Man you really rock this world!

That was clean, simple, fast and fun :)

This was the fastest method also.

Thanks!

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-11 : 09:13:52

rocknpop -> very nice solution :)

rocknpop
Posting Yak Master

201 Posts

Posted - 2010-07-12 : 00:06:05

welcome...please refer to the following for more on such kind of updates:

http://sqlblogcasts.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

--------------------
Rock n Roll with SQL