CODEWITHSUNDEEP
sql-servert-sql
baldie
baldie

Reputation: 27

if value is null get previous value sql server 2008

I have table that looks like this

Id Description
5  Null
4  This is a description
3  This is a description
2  Null
1  Null

I need to create an update statement that will update the null value if the previous value is not null.

Id Description
5  This is a description
4  This is a description
3  This is a description
2  Null
1  Null

Any advice or assistance would be much appreciated.

Upvotes: 2

Views: 2097

Answers (2)

Sean Vieira
Sean Vieira

Reputation: 160043

The most common way (that I am aware of) to do this sort of thing is with a self-join:

-- WARNING: Untested Code
UPDATE YourTable
SET Origin.Description = Proxy.Description
FROM YourTable Origin
JOIN YourTable Proxy
    ON Origin.Id = Proxy.Id - 1
WHERE Origin.Description IS NULL
AND Proxy.Description IS NOT NULL

This will join YourTable to itself so that one row looks like this:

Origin.Id | Origin.Description | Proxy.Id | Proxy.Description
------------------------------------------------------------------
        5 |               NULL |        4 | This is a description

EDIT
If you are not guaranteed always incrementing IDs then you will want to use ROW_NUMBER:

;WITH NumberedRows
AS
    (
    SELECT *
        , ROW_NUMBER() OVER(ORDER BY Id) AS [Row #]
     FROM YourTable
     )
SELECT *
FROM NumberedRows Origin
JOIN NumberedRows Proxy
    ON Origin.Id = Proxy.Id - 1
WHERE Origin.Description IS NULL
AND Proxy.Description IS NOT NULL

Upvotes: 1

sgeddes
sgeddes

Reputation: 62861

I think this is what you're looking for:

update toupdate
set description = updateto.description 
from yourtable toupdate
  join yourtable updateto on updateto.id = toupdate.id - 1
where updateto.description is not null
  and toupdate.description is null;

SQL Fiddle Demo

This produces the following results:

ID  DESCRIPTION
5   This is a description
4   This is a description
3   This is a description
2   (null)
1   (null)

EDIT: As pointed out by comment by Aaron Bertrand.

If your IDs aren't consecutive, you can use the row_number() function to join on instead of the ids:

with cte as (
    select *, row_number() over (order by (select null)) rn
    from yourtable
  )
update toupdate
set description = updateto.description 
from cte toupdate
  join cte updateto on toupdate.rn = updateto.rn - 1
where updateto.description is not null
  and toupdate.description is null;

You can change your order by criteria as needed.

Updated SQL Fiddle

Upvotes: 2

Related Questions