SAP HANA: How to DELETE duplicate records from the Table but keep original record?

 SAP HANA: How to DELETE duplicate records from the Table but keep original record?

How to delete duplicate records from the Table but keep the original record?

Scenario:

DEPTID	DEPTNAME	PRICE
1	Blog	2000
2	Article	5000
3	Resource	7000
4	Book	500
4	Book	500
1	Blog	1000

From the above table, DEPTID 4 have some duplicate records. i.e., DEPTID, DEPTNAME, PRICE columns having the same value.

Solution:

Code:

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:

DEPTID	DEPTNAME	PRICE
1	Blog	2000
2	Article	5000
3	Resource	7000
4	Book	500
1	Blog	1000

 Note:

When we execute the inner query i.e., Sub query we get the result like as shown below:

Code:

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

ROWID

·         For each row in the database, the rowid pseudo column returns the address of the row.

·         Usually, a rowid value uniquely identifies a row in the database.

·         Rowid values have several important uses:

v  They are the fastest way to access a single row.

v  They can show you how the rows in a table are stored.

v  They are unique identifiers for rows in a table.