CODEWITHSUNDEEP
sqloracle-databaseplsql
Rahul
Rahul

Reputation: 199

SQL / PLSQL script to convert values in a database table as unique

I have a (oracle) database table with column called name VARCHAR. I need to update the table to add a unique constraint on this column. But before I can add the unique constraint I need to ensure that all the values in the column are unique. I want to achieve this by adding suffixes like -1, -2, etc in case of duplicate entries.

For example if there are 3 rows having duplicate values in the name column, let's say the value is Hari, I want to change the values as Hari, Hari-1, Hari-2.

How can I achieve this using SQL / PLSQL only?

Upvotes: 0

Views: 83

Answers (3)

VBoka
VBoka

Reputation: 9083

I have used the example that @Tim Biegeleisen provided and adjusted his answer to get you the result you are asking for.

SELECT
    name,
    TO_CHAR(name ||
    case 
        when ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) = 1 then ''
        else to_char ('-' ||  (ROW_NUMBER() OVER (PARTITION BY name ORDER BY id)-1)) 
    end) name_new
FROM yourTable
ORDER BY name;

Demo: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=8d8a1b3f5146b018f92a4c344ed40414

Upvotes: 1

MT0
MT0

Reputation: 168416

You can use MERGE statement and the ROW_NUMBER() analytic function:

MERGE INTO table_name dst
USING (
  SELECT ROWID AS rid,
         name,
         ROW_NUMBER() OVER ( PARTITION BY name ORDER BY ROWNUM ) AS rn
  FROM   table_name
) src
ON ( dst.ROWID = src.rid )
WHEN MATCHED THEN
  UPDATE SET name = CASE rn
                    WHEN 1
                    THEN src.name
                    ELSE src.name || '-' || (rn - 1)
                    END;

Then use:

ALTER TABLE table_name ADD CONSTRAINT table_name__name__u UNIQUE( name );

To create a named unique constraint on the column.

Oracle Setup:

CREATE TABLE table_name ( name ) AS
  SELECT 'Hari'    FROM DUAL UNION ALL
  SELECT 'Hari'    FROM DUAL UNION ALL
  SELECT 'Hari'    FROM DUAL UNION ALL
  SELECT 'Alice'   FROM DUAL UNION ALL
  SELECT 'Bob'     FROM DUAL UNION ALL
  SELECT 'Bob'     FROM DUAL UNION ALL
  SELECT 'Charlie' FROM DUAL;

Output:

SELECT * FROM table_name;

| NAME    |
| :------ |
| Hari    |
| Hari-1  |
| Hari-2  |
| Alice   |
| Bob     |
| Bob-1   |
| Charlie |

db<>fiddle here

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522471

I suggest just using ROW_NUMBER() here to generate the identifier you want:

SELECT
    name,
    TO_CHAR(name || '-' || ROW_NUMBER() OVER (PARTITION BY name ORDER BY id)) AS name_new
FROM yourTable
ORDER BY name;

Demo

Upvotes: 1

Related Questions