UPDATE column for the duplicate entries-CodePudding

I have a table with definition:

CREATE TABLE test(
    id NUMBER(19,0),
    nam VARCHAR2(50) NOT NULL,
    email VARCHAR2(50) NOT NULL
);

and the data

I have to set the same IDs for the entries who have the same EMAIL.

How can I do it? I am using the oracle 18g database.

expected results

CodePudding user response：

If you just want to have the same id for the matching emails:

MERGE INTO test_table tt
USING (SELECT MIN(ID)
            , email
         FROM test_table 
        GROUP BY email) mails
   ON (tt.email = mails.email)
 WHEN MATCHED THEN UPDATE SET tt.id = mails.id;