CODEWITHSUNDEEP
sqloracle11g
user2810293
user2810293

Reputation: 155

how to convert select into update statement

I have a select statement which I need to convert it into Update statement.

SELECT GUID,seq original_seq_no, ROW_NUMBER()
OVER ( PARTITION BY GUID ORDER BY seq) AS new_seq_no
FROM CHK_SEQ;

what actually the above query does is, suppose I have a multiple records with a unique GUID and the value of column SEQ is 6,9,10 in table CHK_SEQ. Then running the above query changes the value of SEQ as 1,2,3 which is new_seq_no.

can anyone please help me with this.

Thanks.

Upvotes: 0

Views: 901

Answers (3)

user330315
user330315

Reputation: 

merge into CHK_SEQ 
using
    SELECT rowid as rid, 
           ROW_NUMBER() OVER (PARTITION BY GUID ORDER BY seq) AS new_seq_no
    FROM CHK_SEQ
) t on (chk_seq.rowid = t.rid)
when matched then update
   set seq = new_seq_no;

Upvotes: 0

Chamal
Chamal

Reputation: 1449

Try this,

with cte 
AS
(
SELECT GUID,seq original_seq_no, ROW_NUMBER()
OVER ( PARTITION BY GUID ORDER BY seq) AS new_seq_no
FROM CHK_SEQ
)

update  CHK_SEQ
set     CHK_SEQ.seq = r.new_seq_no
from    cte r
where CHK_SEQ.seq = r.original_seq_no AND CHK_SEQ.GUID= r.GUID;

Upvotes: 0

Kaf
Kaf

Reputation: 33809

Try this (Sql-Server 2005 and above):

With cte as (
  Select GUID,seq, row_number() over ( partition by GUID order by seq) AS new_seq_no
  From CHK_SEQ
)
Update c
Set c.seq = new_seq_no
From CHK_SEQ c 
     Join cte on c.Guid = cte.Guid and c.seq = cte.seq;

Upvotes: 1

Related Questions