oracle 需要使用row_number()更新id列

mhd8tkvw  于 2023-06-29  发布在  Oracle
关注(0)|答案(1)|浏览(136)

我有一个包含user_iduser_name列的表。我必须将另一列作为ID。我想填充ID如下。
| 用户名|ID| ID |
| - -----|- -----| ------------ |
| ABC|一个| 1 |
| BCD|一个| 1 |
| ABC| 2| 2 |
| ABC|三个| 3 |
| XYZ|一个| 1 |
首先,它需要找到唯一的user_id及其出现次数,然后根据出现次数将1分配给n。
我可以通过使用row_number()获得输出

select row_number() over (partition by user_id order by user_id) as seq ,user_id,user_name from test_csv

我如何用上面查询出来的seq更新ID。

x4shl7ld

x4shl7ld1#

使用MERGE语句并在ROWID伪列上关联:

MERGE INTO test_csv dst
USING (
  SELECT row_number() over (partition by user_id order by user_id) as seq 
  FROM   test_csv
) src
ON (src.ROWID = dst.ROWID)
WHEN MATCHED THEN
  UPDATE
  SET id = seq;

或相关的UPDATE

UPDATE test_csv dst
SET id = (
           SELECT seq
           FROM   (
             SELECT row_number() over (partition by user_id order by user_id) as seq 
             FROM   test_csv
           ) src
           WHERE src.ROWID = dst.ROWID  
         );

其中,对于样本数据:

CREATE TABLE test_csv (User_id, User_name, ID) AS
SELECT 123, 'ABC', CAST(NULL AS NUMBER) FROM DUAL UNION ALL
SELECT 456, 'BCD', NULL FROM DUAL UNION ALL
SELECT 123, 'ABC', NULL FROM DUAL UNION ALL
SELECT 123, 'ABC', NULL FROM DUAL UNION ALL
SELECT 234, 'XYZ', NULL FROM DUAL;

然后,在MERGEUPDATE之后,该表包含:
| 用户名|ID| ID |
| - -----|- -----| ------------ |
| ABC|一个| 1 |
| BCD|一个| 1 |
| ABC| 2| 2 |
| ABC|三个| 3 |
| XYZ|一个| 1 |
fiddle

相关问题