How can I Delete record which have a duplicate values in each date

user_15377265 0
Viewed 82

Hi Dear All I have a table which have Duplicate (or you can say that repeating) record in each date. I just want to delete each duplicate record from each date, how its happened is due to Cross Join Please Help me Its looks like this.. Suppose Table Named is PRODUCT and its column names are like ID and DATE1

ID       DATE1            typ
------------------------------------
100      01-01-2021       A
101      01-01-2021       B
102      01-01-2021       C
100      01-01-2021       A
101      01-01-2021       B
102      01-01-2021       C
100      01-01-2021       A
101      01-01-2021       B
102      01-01-2021       C

In above each ID is repeating every time. I just want to delete each id which is duplicate.

100      01-01-2021       A
101      01-01-2021       B
102      01-01-2021       C

We applied this

delete from your_table a
  where a.rowid > (select min(b.rowid)
                   from your_table b
                   where b.id = a.id
                     and b.date1 = a.date1
                  );

But results show me like this

100      01-01-2021       A
101      01-01-2021       B
102      01-01-2021       B

Or some times it shows C,C,A or some times A,A,C I know there is all data with all possibilities so how can filter just which we just keep table original data how I am working on it is like I have 3 table one have data like

100      01-01-2021       A
101      01-01-2021       B
102      01-01-2021       C
100      10-01-2021       B
101      10-01-2021       C
102      10-01-2021       A
100      20-01-2021       C
101      20-01-2021       A
102      20-01-2021       B

One table have data like dates

01-01-2021
02-01-2021
03-01-2021
04-01-2021
05-01-2021

I want all dates till the next start it appeares all date with same data as like

100      01-01-2021       A
101      01-01-2021       B
102      01-01-2021       C
100      02-01-2021       A
101      02-01-2021       B
102      02-01-2021       C
100      03-01-2021       A
101      03-01-2021       B
102      03-01-2021       C
---
100      10-01-2021       B
101      10-01-2021       C
102      10-01-2021       A
100      11-01-2021       B
101      11-01-2021       C
102      11-01-2021       A

So on so I tried cross join but cross join make each and every rows to make all possibilities so I tried to dell all other dates to be delete that each day get there only ids but know it shows like that how can i get over this please hep me about this regards to all

oracle sql
1 Answers

This is one option to delete duplicates:

delete from your_table a
  where a.rowid > (select min(b.rowid)
                   from your_table b
                   where b.id = a.id
                     and b.date1 = a.date1
                  );

Regarding comments you posted and new column PLUS a new table: this is what you have (at lest, that's what I understood) (sample date in lines #1 - 17; query begins at line #18):

SQL> with
  2  taba (id, date1, col) as
  3    (select 100, date '2021-01-01', 'A' from dual union all
  4     select 101, date '2021-01-01', 'B' from dual union all
  5     select 102, date '2021-01-01', 'C' from dual union all
  6     select 100, date '2021-01-02', 'A' from dual union all
  7     select 101, date '2021-01-02', 'B' from dual union all
  8     select 102, date '2021-01-02', 'C' from dual union all
  9     select 100, date '2021-01-03', 'A' from dual union all
 10     select 101, date '2021-01-03', 'B' from dual union all
 11     select 102, date '2021-01-03', 'C' from dual
 12    ),
 13  tabb (date1) as
 14    (select date '2021-01-01' from dual union all
 15     select date '2021-01-02' from dual union all
 16     select date '2021-01-03' from dual
 17    )
 18  select a.id, a.date1, a.col
 19  from taba a cross join tabb b;

        ID DATE1               C
---------- ------------------- -
       100 01.01.2021 00:00:00 A
       101 01.01.2021 00:00:00 B
       102 01.01.2021 00:00:00 C
       100 02.01.2021 00:00:00 A
       101 02.01.2021 00:00:00 B
       102 02.01.2021 00:00:00 C
       100 03.01.2021 00:00:00 A
       101 03.01.2021 00:00:00 B
       102 03.01.2021 00:00:00 C
       100 01.01.2021 00:00:00 A
       101 01.01.2021 00:00:00 B
<snip>
       101 02.01.2021 00:00:00 B
       102 02.01.2021 00:00:00 C
       100 03.01.2021 00:00:00 A
       101 03.01.2021 00:00:00 B
       102 03.01.2021 00:00:00 C

27 rows selected.

SQL>

Why are you cross-joining these tables? Wouldn't inner join do?

 18  select a.id, a.date1, a.col
 19  from taba a join tabb b on a.date1 = b.date1;

        ID DATE1               C
---------- ------------------- -
       100 01.01.2021 00:00:00 A
       101 01.01.2021 00:00:00 B
       102 01.01.2021 00:00:00 C
       100 02.01.2021 00:00:00 A
       101 02.01.2021 00:00:00 B
       102 02.01.2021 00:00:00 C
       100 03.01.2021 00:00:00 A
       101 03.01.2021 00:00:00 B
       102 03.01.2021 00:00:00 C

9 rows selected.

SQL>

Or perhaps SELECT DISTINCT (in cross join)?

 18  select distinct a.id, a.date1, a.col
 19  from taba a cross join tabb;

        ID DATE1               C
---------- ------------------- -
       100 02.01.2021 00:00:00 A
       102 01.01.2021 00:00:00 C
       101 02.01.2021 00:00:00 B
       102 03.01.2021 00:00:00 C
       100 03.01.2021 00:00:00 A
       100 01.01.2021 00:00:00 A
       101 03.01.2021 00:00:00 B
       101 01.01.2021 00:00:00 B
       102 02.01.2021 00:00:00 C

9 rows selected.

SQL>

After all, it seems that you aren't actually deleting rows from any table(s), but want to restrict number of rows you get as a result of your select statement ...

user_90979060
Related