Skip to Content

SqlScript delete duplicates from local table variable

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 .

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

  • Get RSS Feed

2 Answers

  • Best Answer
    Mar 15, 2018 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    
    Add comment
    10|10000 characters needed characters exceeded

  • Mar 15, 2018 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...

    Add comment
    10|10000 characters needed characters exceeded