Skip to Content
0

How to delete Duplicate row but keep original in SAP HANA

Nov 13, 2017 at 12:25 PM

614

avatar image

Hi,

This is my table :

I want to delete duplicate records but keep original.

anyone give suggestion...

i tried this code but it wont works:

CODE 1:

WITH DEPTCTE as
(
select *,Row_Number() over (partition by DEPTID,DEPTNAME order by DEPTID) as ROW_NUMBER
from "ADZSUPRI_PRACTICE"."cpy_tab_Department"
)
DELETE from DEPTCTE where ROW_NUMBER <> 1;

From the above code, delete statement is not working it throws an error.

And this code deletes duplicates records and also the original.

CODE 2:
delete  from "ADZSUPRI_PRACTICE"."cpy_tab_Department"
where "$rowid$" in
(
SELECT   LEAD("$rowid$") over (partition by DEPTID,DEPTNAME order by DEPTID,DEPTNAME) 
from "ADZSUPRI_PRACTICE"."cpy_tab_Department" Group by DEPTID,DEPTNAME
)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Kabilarasan R Nov 13, 2017 at 12:47 PM
-2

I found an answer for the above question:

delete  from "ADZSUPRI_PRACTICE"."cpy_tab_Department"
where "$rowid$" in
(
SELECT   LEAD("$rowid$") over (partition by DEPTID,DEPTNAME,"price" order by DEPTID,DEPTNAME) 
from "ADZSUPRI_PRACTICE"."cpy_tab_Department" 
)

Result:


Show 1 Share
10 |10000 characters needed characters left characters exceeded

Using the "$rowid$" is not supported and should not be part of your production code.

Getting rid of duplicates can be done in several other ways, e.g. SELECT DISTINCT into a target table, using added ID columns that provide the uniqueness that the table design lacks, ...

Trying to do this in-place in a single command, even though the table design is flawed (no primary key) just makes your life harder.

0