We help IT Professionals succeed at work.

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

skaleem1
skaleem1 asked
on
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

View Solution Only

Commented:
try
declare @whereusedbottom varchar(25)

set @whereusedbottom = ''

update MatrixWhereUsed 
	set @whereusedbottom = coalesce(whereusedbottom, @whereusedbottom), 
	whereusedbottom = case when whereusedbottom is null then @whereusedbottom else whereusedbottom end

Open in new window

ralmada

Commented:
or just
declare @whereusedbottom varchar(25)

set @whereusedbottom = ''

update MatrixWhereUsed 
	set @whereusedbottom = coalesce(whereusedbottom, @whereusedbottom), 
	whereusedbottom = coalesce(whereusedbottom, @whereusedbottom)

Open in new window

dqmq

Commented:
update MatrixWhereUsed T1
      set whereusedbottom =
(select T2.whereusedbottom from MaxtrixWhereUsed T2
   where T2.whereusedId =
      (select max(T3.WhereUsedID) from MaxtrixWhereUsed T3
            where t3.WhereUsedid < T1.WhereUsedID
                and t3.whereusedBottom is not null
     )
)
where t1.whereusedbottom is not null

 
skaleem1

Author

Commented:
thanks a lot Ralmada. This worked great.
dqmq

Commented:
Yikes!  That's dangerous stuff.  You are relying on the order rows are updated when the dbms guarantees no such order.  Don't think it's a reliable solution.


 



Corrected syntax below:
update MatrixWhereUsed
      set whereusedbottom =
(select T2.whereusedbottom from MatrixWhereUsed T2
   where T2.whereusedId =
      (select max(T3.WhereUsedID) from MatrixWhereUsed T3
            where t3.WhereUsedid < t1.WhereUsedID
                and t3.whereusedBottom is not null
     )
)
from matrixwhereused t1
where t1.whereusedbottom is  null