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.

  • Thanks, ERALPER YILMAZ

    It is a best way to delete duplicates.

  • Get RSS Feed

2 Answers

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

    MATNR       WERKS   GJAHR   MONAT   STOCK
    1000000000  1000    2017    05      0    
    1000000001  1000    2018    03      3    
    Add comment
    10|10000 characters needed characters exceeded

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

    Add comment
    10|10000 characters needed characters exceeded