CODEWITHSUNDEEP
sql
Xiao Qiang
Xiao Qiang

Reputation: 83

How to update rows based on previous records in SQL?

I have a table as following:

ID  MARKET
2   INDIV
2   CPEINT
2   INDIV
2   INDIV
2   INDIV
2   INDIV
2   INDIV
5   INDIV
5   INDIV
5   CPEINT
5   INDIV
5   INDIV
5   INDIV
5   INDIV
5   INDIV
5   INDIV

What I want is to update the records in MARKET column based on the previous rows in ID and MARKET. If previous MARKET is CPEINT and the current ID is the same as previous ID, then replace the current MARKET as CPEINT. The output I am looking for will be:

ID  MARKET
2   INDIV
2   CPEINT
2   CPEINT
2   CPEINT
2   CPEINT
2   CPEINT
2   CPEINT
5   INDIV
5   INDIV
5   CPEINT
5   CPEINT
5   CPEINT
5   CPEINT
5   CPEINT
5   CPEINT
5   CPEINT

Can I use something like MERGE or UPDATE to do this?

Many thanks!

Upvotes: 2

Views: 7196

Answers (2)

TechDo
TechDo

Reputation: 18639

Please try query for SQL server:

;with T as(
    select 
        ROW_NUMBER() over (order by ID) RNum, * 
    From 
        YourTable)
    update c set MARKET='CPEINT'
    from T c join
    (select a.RNum, a.ID
    From T a LEFT JOIN T b on a.RNum=b.RNum+1
    where a.ID=b.ID and b.MARKET='CPEINT')x on c.RNum>=x.RNum AND c.ID=x.ID

Upvotes: 3

Dan Bracuk
Dan Bracuk

Reputation: 20804

This may or may not satisfy your requirements. 

update yourtable
set market = 'CPEINT'
where id in 
(select id 
from yourtable
where market = 'CPEINT')
and market = 'MARKET'

Upvotes: 0

Related Questions