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

how to convert row to column in oracle sql with example
how to display column values in a row in oracle
how to convert rows into columns in sql query
fetch from cursor and insert into table oracle
sql unique constraint multiple columns
how to convert row to column in oracle sql with example without pivot
oracle pivot multiple columns
how to insert data into table by using pl/sql procedure

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?

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

6 Performing SQL Operations from PL/SQL, By extending SQL, PL/SQL offers a unique combination of power and ease of use​. INSERT adds new rows of data to database tables; UPDATE modifies rows; Doing other operations, such as procedure calls, might change the value of  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 t

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

How to Convert Rows to Columns and Back Again with SQL (Aka , Or you can nab the create table scripts at the bottom of this post. Ready? Let's begin! Convert Rows to Columns (PIVOT). Oracle Database 11g introduced the pivot operator. The column that has the values defining the new columns; What The distinct contains the columns defining a particular event. SQL UNIQUE Constraint. The UNIQUE constraint ensures that all values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint.

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

SQL UNIQUE Constraint, SQL Create DB SQL Drop DB SQL Backup DB SQL Create Table SQL Drop Table SQL The UNIQUE constraint ensures that all values in a column are different. However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. MySQL / SQL Server / Oracle / MS Access:. Oracle / PLSQL: Unique Constraints. This Oracle tutorial explains how to create, drop, disable, and enable unique constraints in Oracle with syntax and examples. What is a unique constraint in Oracle? A unique constraint is a single field or combination of fields that uniquely defines a record.

Oracle / PLSQL: DISTINCT Clause, The Oracle DISTINCT clause is used to remove duplicates from the result set. The DISTINCT clause can only be used with SELECT statements. SELECT DISTINCT expressions FROM tables [WHERE conditions]; using the DISTINCT clause in your SQL statement, your result set will include NULL as a distinct value​. Summary: in this tutorial, you will learn about the PL/SQL VARRAY and how to manipulate elements of a VARRAY effectively. Introduction to PL/SQL VARRAY. VARRAY stands for the variable-sized array. A VARRAY is single-dimensional collections of elements with the same data type.

Oracle / PLSQL: CREATE TABLE Statement, The Oracle CREATE TABLE statement allows you to create and define a table. null" and if this value is left blank, the database assumes "null" as the default. Sql script to select and insert data fron one table to another table How to generate create/alter script for a table in SQL server, only with SQL/t-SQL scripting Need help in update query in SQL server

Oracle SELECT DISTINCT By Practical Examples, This tutorial shows you how to use the Oracle SELECT DISTINCT statement to get distinct rows In this statement, the values in the column_1 of the table are compared to determine the duplicates. Note that DISTINCT is synonym of UNIQUE which is not SQL standard. See the contacts table in the sample database:. The table construct can then be queried with standard SQL just like any other Oracle table with SQL. Listing 1.1 shows an example of how the CAST function can be used inside an anonymous PL/SQL block to read from a PL/SQL collection defined by a declared TYPE. CAST is used here to sort the resulting rows in reverse alphabetical order.

Comments
  • Like this solution the most. Thanks much.
  • @Rahul Comments are ephemeral and can be deleted; instead you can upvote any (or all) helpful solutions using the arrows to the left of the question and, if an answer has solved your issue then you can click the check-mark below the voting buttons to mark it as your preferred answer and that will let other users know that you have a solution and are not looking for new answers (although they could still give them if they want).
  • This will add a number to all records, not only the duplicated ones. I would suggest name || LTRIM('-0' FROM '-'||to_char(ROW_NUMBER() OVER (PARTITION BY name ORDER BY id)-1))
  • @WernfriedDomscheit I was aware of this when I posted, and see the MT0 answer for a workaround. Practically speaking, the value in what the OP is suggesting is in being able to distinguish one "duplicate" from another. The answer I gave certainly does that, although it does place a number even against the "original" data point.