Skip to Content

How to delete Duplicate row but keep original in SAP HANA

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
)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • 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:

    Add comment
    10|10000 characters needed 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.