Updating across row and column in SQL

Question

Bit of a strange question. Entries in my table have three relevant columns:

drug
date_1
date_2
seq_id

I am trying to make it such that every drug with the lowest seq_id has a date_2 equal to the date_1 of the drug with the second lowest seq_id (the second lowest will always be lowest + 1) with the same drug name.

Does this make sense?

I don't have any real clue how to go about doing this. I've managed to make a query that returns the date_1 and seq_id of every second-lowest record, and now I want to make it such that every row with a seq_id exactly one lower than any seq_id in this set of records has date_2 = date_1 of the record with the 1-higher seq-id.

God this is really confusing, I hope it comes through.

Answer 1

The SQLServer version of this could look like this.

First we want the drug table and some values

create table #drugs (drug nvarchar(10),date_1 date, date_2 date, seq_id int)
 insert into #drugs values ('sildenafil',NULL,NULL,1)
                          ,('sildenafil',NULL,NULL,2)
                          ,('sildenafil',NULL,NULL,3)
                          ,('tadalafil',NULL,NULL,1)
                          ,('tadalafil',NULL,NULL,2)
                          ,('tadalafil',NULL,NULL,3)
                          ,('vardenafil',NULL,NULL,1)
                          ,('vardenafil',NULL,NULL,2)
                          ,('vardenafil',NULL,NULL,3)

Then we need to update the next lowest seq_id with a date

update #drugs 
  set date_1 = getdate() 
   where seq_id = 
   (select min(seq_id)+1 
    from #drugs d2 
    where #drugs.drug = d2.drug)

And the update the lowest date_2 value with the date_1 value from the second lowest seq_id - This is update with a join with the strange looking SQL-Server syntax and is

update D1
  set D1.date_2 = d2.date_1
  From #drugs D1
   inner join 
    (select drug,date_1,seq_id
     from #drugs d1 
     where seq_id = (select min(seq_id)+1 from #drugs)
    ) as d2
     on d1.drug = d2.drug and d1.seq_id = d2.seq_id-1

And Clean up

Drop Table #drugs