Skip to Content
avatar image
Former Member

What is the SQL to delete the first N records from a table?

I saw in discussions a solution as below :

delete from MY_TABLE where (<key_field1>, <key_field2>, <...>) in (select top <N> <key_field1>, <key_field2>, <...> from MY_TABLE order by <sort_field_list>);

But in my case I can't compare the fields as it can delete other duplicate records which are in table after first N rows. I want to delete only first N rows.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Sep 27, 2017 at 08:25 AM

    Hi,

    You can modify your query as below.

    delete from MY_TABLE where (<key_field1>, <key_field2>, <...>) in

    (select top <N> <key_field1>, <key_field2>, <...> from

    (select distinct * from MY_TABLE order by <sort_field_list>));

    Regards,

    Jyoti

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 03, 2017 at 03:01 AM
    -2

    you can use "$rowid$" . (don't use capital or it won't work)
    no one mentioned but each table in sap_hana has a hidden column which is "$rowid$"
    you can use below code;

    NOTE : Please use $rowid$ at your own risk. it is undocumented, unsupported and totally not recommended. ( Lars Breddemann has already explained technically as below why we should not use it)

    delete from "TABLE_NAME" where "$rowid$" < 1000

    The value of the $rowid$ column is related to the partition ID. For a table without a partition, the value ranges from 1 to 18014398509481984. On a partitioned table, the value for partition 1 starts at 18014398509481985; partition 2 starts at 36028797018963969; partition 3 starts at 54043195528445953; and so on. Means from $rowid$ you can get your partition ID as well (if you need that info ). Lars Breddemann can give more info on that.

    I am telling again; $rowid$ column is not officially supported by SAP. Therefore, this option is to be used with caution and at your own risk. Also, due to triggering some internal processes, you will incur a performance penalty on queries that contain the hidden column. In general, it is better to choose features that SAP HANA officially support. To be honest changing the behaviour of $rowid$ inside the sap hana landscape is probably harder than shutting down whole company. Even putting an extra comma inside any of the HANA source code probably requires board meetings, 500-600 internal discussions, views of sap technical architects and some democratic votings inside. Anyway none of those should courage us to ise $rowid$. Better go for standard approaches which already exists inside.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 02, 2017 at 02:26 PM
    -2

    Sreeram,

    I regularly scan few SAP HANA system tables like M_BACKUP_CATALOG for top (latest) entries in the table using below SQL. It should work for DELETE as well:

    select TOP 2 ENTRY_ID, SYS_START_TIME, SYS_END_TIME, STATE_NAME from M_BACKUP_CATALOG where entry_type_name like 'complete%' order by sys_start_time desc

    Modify the query to meet the conditions you are looking for and give it a try. Please remember to take backup before you try anything :)

    Srinivas

    Add comment
    10|10000 characters needed characters exceeded

    • Hello Srinivas,

      unfortunately, it is not that easy. There is no "DELETE TOP 2 FROM...." in HANA.

      The option mentioned by Joyti was already proposed recently and seems to be a valid one.

      BR, Bojan