CODEWITHSUNDEEP
sqlsql-servert-sql
Tibomso
Tibomso

Reputation: 373

Inserting the previous row

I have the following table:

DECLARE @TempTable TABLE
(
    ID int,
    Lastname nvarchar(50),
    Firstname nvarchar(50),
    Age int
)

insert into @TempTable (ID, Lastname, Firstname, Age)
VALUES(1, 'Smith', NULL, NULL),
    (2, NULL, 'Will', 40),
    (3, NULL, 'Jaden', 20),
    (4, 'Matt', NULL, NULL),
    (5, NULL, 'Damon', 39)

enter image description here

I want to change the table using 'update' as follows:

enter image description here

I know about the functions 'lag' and 'lead'. But as I understand they can not be used in UPDATE expressions

Upvotes: 3

Views: 102

Answers (3)

LukStorms
LukStorms

Reputation: 29647

You can also get those results via a Recursive CTE

Example:

DECLARE @TempTable TABLE
(
    ID int,
    Lastname nvarchar(50),
    Firstname nvarchar(50),
    Age int
)

insert into @TempTable (ID, Lastname, Firstname, Age) values
(1, 'Smith', NULL, NULL),
(2, NULL, 'Will', 40),
(3, NULL, 'Jaden', 20),
(4, 'Matt', NULL, NULL),
(5, NULL, 'Damon', 39);

;with RCTE AS
(
  select ID, LastName, ID AS [NewID], LastName as NewLastName
  from @TempTable
  where LastName IS NULL

  union all

  select r.ID, r.LastName, t.ID, t.LastName
  from RCTE r
  join @TempTable t on t.ID = r.[NewID] - 1
  where r.NewLastName is null
)
update t
set LastName = RCTE.NewLastName
from @TempTable t
join RCTE on (RCTE.ID = t.ID and RCTE.NewLastName IS NOT NULL);

delete 
from @TempTable 
where FirstName is null;

select * 
from @TempTable 
order by ID;

Upvotes: 0

Menelaos
Menelaos

Reputation: 25727

Microsoft SQL solution

select id ,

(

    select Lastname from @TempTable c  where c.ID < p.id AND c.Lastname is not null group by Lastname
 order by max(id) desc OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY  

) as Lastname, 

FirstName,
Age

from @TempTable p
where Firstname is not null;

Fiddle: http://sqlfiddle.com/#!18/4922f/15

Example: http://rextester.com/IKG65840

Upvotes: 2

sgeddes
sgeddes

Reputation: 62831

Here's one option using a correlated subquery:

update t 
set lastname = (select top 1 lastname 
                from @TempTable t2 
                where t2.lastname is not null and
                       t.id > t2.id 
                order by t2.lastname) 
from @TempTable t
where firstname is not null;

delete from @TempTable 
where firstname is null;

select * from @TempTable;

Upvotes: 1

Related Questions