Deleting duplicate rows from a table

[#1]

To delete duplicate rows from a table I have been using (in Oracle)

      delete from <table> where rowid in

      (

         select LEAD(rowid)

         over (partition by <col1>, <col2> order by <col1>)

         from <table>

      );

As I understand, HANA does not support the rowid.

How should I delete duplicate rows in a table on HANA?

Regards,

Ole K. Røsberg

[Vitaliy-R]

Not sure if this is the most elegant solution, but worked for me (at least seems to ;-))

I had two columns containing combination of duplicated values to remove: col1 and col2

delete from table

where "$rowid$" in ( select ROW_ID from

(SELECT

  ROW_NUMBER() OVER (PARTITION BY col1, col2) as RN,

  "$rowid$" as ROW_ID,

  col1,

  col2

  FROM table

  ORDER BY 3,  2,  1)

where RN>1);

Cheers,

-Vitaliy

[americo_goncalvesqualho]

Hi Vitaliy,

I had the same issue during an system copy using export and import and using the standard method of create a temp table like the original and doing a select distinct to it and so on did not work. Turned out that the entire table was distinct considering the combination of all columns. I've ran your solution and it worked like a charm !

However i would like to understand where the columne "$rowid$" came from.Is it a meta-column of HANA tables to identify the rowID or it is the result of the function ROW_NUMBER() ?

If you don't mind, can you explain a little more detailed your solution ?

Thumbs up and thanks a lot !

[Former Member]

Hi,

ROWID exists in HANA. it is accessed as "$rowid$".

And the query to delete the duplicate entries is as below :

delete  from <table_name>

where "$rowid$" in

(

SELECT   LEAD("$rowid$") over (partition by <duplicate_column> order by <duplicate_column>) from <table_Test>

)

Thanks,

Poorna

[codegagan]

This seems to be the simplest way. Thanks a lot

[Former Member]

Hi ,

Below could be one of the solutions:

delete from <table> where <column_with_duplicate_enrties> = (SELECT top 1

    <column_with_duplicate_enrties>

FROM

   <table>

GROUP BY

      <column_with_duplicate_enrties>

HAVING

    COUNT(*) > 1)

execute this inside a loop for 'n' times where n = count of rows with non-distinct entries.

Thanks & Regards,

Poorna

[Former Member]

Hi Ole Roseberg,

1)Select the distinct records into a New Table

CREATE COLUMN TABLE "schema"."new"

LIKE "schema"."old";

SELECT DISTINCT *

FROM "schema"."old"

INTO "schema"."new";

2)Delete the records from Old Table

DELETE FROM "schema"."old"

3)Insert New table data back in to Old Table and drop the new table.

INSERT INTO "schema"."old" SELECT * FROM "schema"."new";

DROP TABLE "schema"."new";

Cheers,

Safiyu

[Former Member]

Hi Rosberg,

You can add a temp column, and update this column to sequence, then take is as rowid in Oracle DB.

alter table table1 add(tmp_col integer);
update table1 set tmp_col = myseq.nextval;
delete from table1 a where tmp_col < (select max(tmp_col) from table1 b where a.col1 = b.col1 and a.col2 = b.col2);
alter table table1 drop(tmp_col);

[rainer_winkler]

Hello Mr. Røsberg,

I am curious to know the final answer. In the moment I can only guess what it could be. By the way can the question be limited to column tables?

The problem is probably that column tables are internally not systematically handled by a robust row id, I suppose for reason of performance and robustness. Column Tables have a main and a delta store. In case of a running delta merge, they have two main and two delta store.

My idea would be to write a coding, that selects all lines that are duplicate, deletes them and then inserts them again without duplicates.

Has someone a better proposal?

Kind Regards

Rainer Winkler

[shanthi_bhaskar]

You can use Select Distinct so that it doesnt fetch the duplicates from the table 

[Former Member]

I know that select distinct does not fetch the duplicates.

My question is how to delete the duplicates from the table.

[shanthi_bhaskar]

check this post

http://stackoverflow.com/questions/985384/delete-duplicate-records-from-a-sql-table-without-a-primar...