We help IT Professionals succeed at work.
Get Started

How to replace NULL rows in a column by the row entry immediately above

299 Views
Last Modified: 2012-06-27
I have a table that has three columns (first column is the ID and is the primary key). The second column named WhereUsedBottom and the third column named WUOne.

The values in the table are as shown in the image below. All I want is to write a transact SQL statement (without a cursor) that replaces the null/empty values in the second column (WhereUsedBottom ) by the entry immediately above. E.g. the 2nd and 3rd rows in the WhereUsedBottom column are null and should be replaced by the value ABC-123-001 (the entry immediately above), 5th row is null and should be replaced by the value ABC-456-001 (the entry immediately above), and, 7th and 8th rows are null and should be replaced by the value ABC-789-002 (the entry immediately above).

I can do it using a cursor (see the code below), but it is not efficient as the real scenario involves more than 90,000 rows.

Can you please help me write a query to do that without a cursor?


DECLARE @WhereUsedBottomID INT, @WhereUsedBottomMost VARCHAR(25), @BottomNotNull VARCHAR(25)

	DECLARE WU_Bottom_cursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR 
		SELECT WhereUsedID, WhereUsedBottom
		FROM MatrixWhereUsed
		OPEN WU_Bottom_cursor
		FETCH NEXT FROM WU_Bottom_cursor INTO @WhereUsedBottomID, @WhereUsedBottomMost
		WHILE (@@fetch_status=0)
		BEGIN

		IF @WhereUsedBottomMost IS NOT NULL
			BEGIN
				SET @BottomNotNull=@WhereUsedBottomMost
			END
		ELSE
			BEGIN
				IF @BottomNotNull IS NOT NULL
					UPDATE MatrixWhereUsed SET WhereUsedBottom=@BottomNotNull WHERE WhereUsedID=@WhereUsedBottomID
			END

--		IF @WUOneC IS NOT NULL
--			SET @WUOnePrevious=@WUOneC
			
		
		FETCH NEXT FROM WU_Bottom_cursor INTO @WhereUsedBottomID, @WhereUsedBottomMost
		END
		CLOSE WU_Bottom_cursor
		DEALLOCATE WU_Bottom_cursor

Open in new window

Table.JPG
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE