Update in oracle sql: multiple rows in 1 table

Question

I'm new to SQL, and I'm not very good at more complex queries and functions.

So, I have this 1 table with sales:

 id   date         seller_name   buyer_name
---- ------------ ------------- ------------
 1    2015-02-02   null          Adrian
 1    2013-05-02   null          John B
 1    2007-11-15   null          Chris F
 2    2014-07-12   null          Jane A
 2    2011-06-05   null          Ted D
 2    2010-08-22   null          Maryanne A
 3    2015-12-02   null          Don P
 3    2012-11-07   null          Chris T
 3    2011-10-02   null          James O

I would like to update seller_namefor each id, putting buyer_namefrom the previous sale as seller_name on a newer sale date. For example, for id 1, John B will be seller at 2015-02-02and buyer at 2013-05-02. It makes sense?

PS This is an ideal case, the table is large, and the identifiers are not ordered so neatly.

Answer 1

merge into your_table a 
using ( select rowid rid,
        lead(buyer_name, 1) over (partition by id order by date desc) seller
        from   your_table 
       ) b
       on (a.rowid = b.rid )
when matched then update set a.seller_name= b.seller;

: Merge into . , using , , rowid, . lead n , . , , id , , . , .

Answer 2

merge into sandeep24nov16_2 table1
using(select rowid r, lag(buyer_name) over (partition by id order by "DATE" asc) update_value from sandeep24nov16_2 ) table2
on (table1.rowid=table2.r)
when matched then update set table1.seller_name=table2.update_value;

merge into sandeep24nov16_2 table1
using(select rowid r, lead(buyer_name) over (partition by id order by "DATE" desc) update_value from sandeep24nov16_2 ) table2
on (table1.rowid=table2.r)
when matched then update set table1.seller_name=table2.update_value;

Answer 3

select a.*,
       lag(buyer_name, 1) over(partition by id order by sale_date) seller_name
   from <your_table> a;