update in oracle sql : multiple rows in 1 table

Question

I am new to SQL and I am no good with more advanced 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 the seller_name for each id, by putting the buyer_name from previous sale as seller_name to newer sale date. For example, for on id 1 John B would then be seller in 2015-02-02 and buyer in 2013-05-02. Does that make sense?

P.S. This is the perfect case, the table is big and the ids are not ordered so neat.

I think what are you looking for are the lead and lag functions. — Mihai-Daniel Virna
– Mihai-Daniel Virna, Commented Feb 24, 2016 at 8:15

Aleksej · Accepted Answer · 2016-02-24 08:38:32Z

5

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;

Explanation : Merge into statement performs different operations based on matched or not matched criterias. Here you have to merge into your table, in the using having the new values that you want to take and also the rowid which will be your matching key. The lead function gets the result from the next n rows depending on what number you specify after the comma. After specifying how many rows to jump you also specify on what part to work, which in your case is partitioned by id and ordered by date so you can get the seller, who was the previous buyer. Hope this clears it up a bit.

edited Feb 24, 2016 at 8:38

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

answered Feb 24, 2016 at 8:28

Mihai-Daniel Virna

9949 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sandeep · Accepted Answer · 2016-02-24 11:18:04Z

1

Either of the below query can be used to perform the desire action

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;

or

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;

answered Feb 24, 2016 at 11:18

Sandeep

8164 silver badges8 bronze badges

Comments

Shriram M. · Accepted Answer · 2016-02-24 08:46:08Z

0

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

answered Feb 24, 2016 at 8:46

Shriram M.

3831 silver badge7 bronze badges

2 Comments

Mihai-Daniel Virna Over a year ago

This is just the data but OP actually needs an update statement

Shriram M. Over a year ago

@NullException - Sure, I agree, for which your solution is better. I was trying with lag instead of lead. So it is just another way.

Collectives™ on Stack Overflow

update in oracle sql : multiple rows in 1 table

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related