How to update table using rank function-CodePudding

create table test(
e_id NUMBER(10),
code NUMBER(10),
active NUMBER(1)
);

INSERT INTO test VALUES(11,0,NULL);
INSERT INTO test VALUES(11,0,NULL);
INSERT INTO test VALUES(11,3,NULL);
INSERT INTO test VALUES(12,1,NULL);
INSERT INTO test VALUES(12,3,NULL);
INSERT INTO test VALUES(12,4,NULL);

DB version: Oracle 18c

I need to find the minimum code from the table test grouping e_id. If we have same values after finding the minimum one then need to update the active column to 1. Basically, need to find out the minimum code value and update the first record to 1 in active column.

My attempt:

SELECT e_id, code, rnk FROM (
SELECT t.*,
RANK() OVER (PARTITION BY e_id ORDER BY code ASC) AS rnk
FROM test t
);

This will give me the rank of the rows and if I filter based rnk=1 then will give the result but how to update the table in this scenario?

Expected output:

 ------ ------ -------- -- 
| E_ID | CODE | ACTIVE |  |
 ------ ------ -------- -- 
|   11 |    0 | 1      |  |
|   11 |    0 | NULL   |  |
|   11 |    3 | NULL   |  |
|   12 |    1 | 1      |  |
|   12 |    3 | NULL   |  |
|   12 |    4 | NULL   |  |
 ------ ------ -------- --

CodePudding user response：

Here's one option; row_number is better in this case because rank will "mark" all duplicate rows with 1, but you need only one.

Before:

SQL> select * from test order by e_id, code;

      E_ID       CODE     ACTIVE
---------- ---------- ----------
        11          0
        11          0
        11          3
        12          1
        12          3
        12          4

6 rows selected.

Update (actually, merge):

SQL> merge into test a
  2    using (select s.rid
  3           from (select rowid rid,
  4                        row_number() over (partition by t.e_id order by t.code) rnk
  5                 from test t
  6                ) s
  7           where s.rnk = 1
  8          ) x
  9    on (x.rid = a.rowid)
 10  when matched then update set
 11    a.active = 1;

2 rows merged.

After:

SQL> select * from test order by e_id, code;

      E_ID       CODE     ACTIVE
---------- ---------- ----------
        11          0          1
        11          0
        11          3
        12          1          1
        12          3
        12          4

6 rows selected.

SQL>