Skip to Content

SqlScript delete duplicates from local table variable

Mar 15 at 03:51 AM


avatar image

Hi, experts.

I have a issue. I need to delete duplicates from local table variable in AMDP.

I find this

delete from tab
where "$rowid$" in
SELECT LEAD($rowid$) over (partition by field1, field2) from tab
) ;

But for local table variable it is do not work .

10 |10000 characters needed characters left characters exceeded

To identify and delete duplicates using SQLScript, another method can be using Row_Number() with Partition By clause. The columns that are accepted to define duplicate case can be used in Partition By clause of the Row_Number() function.

Here is a sample

et_data = select kunnr, vbeln from (
    select row_number ( ) over ( partition by kunnr order by vbeln) as rn, * from :lt_data
    ) where rn = 1;

Above SQL returns first order of a customer

If you add more columns in Partition By clause (here is only kunnr used), or all columns than you will have an exact duplicate of the row data.



It is a best way to delete duplicates.

* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Lars Breddemann
Mar 15 at 06:10 AM

Now this is a prime example for why one should try to ask about how to achieve something instead of how to do this little step in the overall solution.

Your problem actually is: get me the most current months record for every combination of material, factory and year.

There's a fairly straight forward approach for that.

  1. find out the most current month for every material|factory|year.
  2. select only those records from the table that are about the most current month.

Step 1 is an easy MAX() aggregation.

Step 2 is an easy filtering via INNER JOIN

select s.matnr, s.werks, s.gjahr, s.monat, s.stock
from stock s
inner join (select matnr, werks, gjahr, max(monat) as max_month
            from stock
            group by matnr, werks, gjahr) ms
       on ( s.matnr, s.werks, s.gjahr, s.monat) 
          = (ms.matnr, ms.werks, ms.gjahr, ms.max_month);

With your demo data this yields:

1000000000  1000    2017    05      0    
1000000001  1000    2018    03      3    
10 |10000 characters needed characters left characters exceeded
Lars Breddemann
Mar 15 at 04:36 AM

DELETE on table variables is only available starting with HANA 2. Do you use a HANA 2?

Besides that, using the internal column store column $rowid$ has never been supported - using it to eliminate duplicate records in the absence of a key is not the brightest idea.

What you can easily do in your case is to simply SELECT DISTINCT from your table variable.

tab_dupes = SELECT ... FROM all_my_dupes;
tab_nodupes = SELECT DISTINCT * FROM :tab_dupes;

That's it. No weird internal column stuff, no difficult update on table variables.

For scenarios where you have duplicates only on the subset of columns, e.g. your key-columns to-be, you can use the standard options like picking the largest value (if "largest" is unique) or just get the first/last entry. But that's all standard SQL, too...

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

Thanks, Lars.

I am using Hana 1.0 SPS12.

I am trying to using DISTINCT but more complex task.

I have i db table with stocks history.For example

matnr werks gjahr monat stock

1000000000 1000 2017 05 0

1000000000 1000 2017 04 2

1000000000 1000 2017 01 1

1000000001 1000 2018 02 2

1000000001 1000 2018 03 3

1000000001 1000 2018 01 1

And i need to get the last stock for each group of (matnr+werks)

For using distinct i need to select only matnr+werks, but how i can get last stock number ?


Thanks, Lars.

It is resolve my problem.