CODEWITHSUNDEEP
sqloracle-database
Bora.Ozgur
Bora.Ozgur

Reputation: 65

How to delete reversed value on Oracle Database by using Query?

Lets say i have some data on Oracle database like what i mentioned above .

TABLE 

       COLUMN1 | COLUMN2 | Quantity

        100    |  2000   |     1
       2000    |  100    |     5
       3000    |  100    |     2

As you can see column1 and column2 is reversed data . What is the efficient way to clean this data from oracleDb . I am not concerned about which one might delete . My main purpose is if table includes reverse data one of them should be deleted . It is enough for me .

Upvotes: 1

Views: 143

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can delete the data in many ways. I might suggest:

delete from t
    where t.col1 > t.col2 or
          exists (select 1
                  from t t2
                  where t2.col1 = t.col2 and t2.col2 = t.col1
                 );

Then . . . the more important thing. Prevent this from happening in the future with a unique constraint/index:

create unique index unq_t_col1_col2 on
     t(least(col1, col2), greatest(col1, col2));

Actually, I would fix the data so col1 < col2 always and add the constraints:

unique (col1, col2),
check (col1 < col2)

Nothing like keeping the data clean to keep your database safe.

Upvotes: 0

Popeye
Popeye

Reputation: 35900

The simplest way of doing it is using EXISTS as shown in the following example:

Oracle Data setup:

SQL> CREATE TABLE TBL (COL1, COL2, QUANTITY)
  2  AS SELECT * FROM
  3  (
  4  SELECT 100, 2000, 1 FROM DUAL UNION ALL
  5  SELECT 2000, 100, 5 FROM DUAL UNION ALL
  6  SELECT 3000, 100, 2 FROM DUAL
  7  );

Table created.

Current data:

SQL> SELECT * FROM TBL;

      COL1       COL2   QUANTITY
---------- ---------- ----------
       100       2000          1
      2000        100          5
      3000        100          2

Query to delete the duplicate:

SQL> DELETE FROM TBL T
  2  WHERE EXISTS (
  3          SELECT 1
  4          FROM TBL TIN
  5          WHERE T.COL1 = TIN.COL2
  6              AND T.COL2 = TIN.COL1
  7              AND T.ROWID > TIN.ROWID
  8      );

1 row deleted.

Current view of the data:

SQL> SELECT * FROM TBL;

      COL1       COL2   QUANTITY
---------- ---------- ----------
       100       2000          1
      3000        100          2

Cheers!!

Upvotes: 1

Ronald
Ronald

Reputation: 2872

Let me call your table T.

select *
from T T1 join T T2
     on T1.column1 = T2.column2
     and T1.column2 = T2.column1;

Gives you the pairs. Each pair occurs twice, because of the symmetry.

In order to delete them, you can do

delete from T
where rowid in (
    select T1.rowid
    from T T1 join T T2
         on T1.column1 = T2.column2
         and T1.column2 = T2.column1
    where T1.rowid < T2.rowid
);

Note: I didn't test this. I leave that up to you.

Upvotes: 0

Related Questions