SQL - Delete first 1000 records

SQL - Delete first 1000 records

  

Hi community!


I would like to ask what is the right SQL statement for deleting the first 1000 records? I know this is a noob question but LIMIT is not working? I keep getting the 'SQL command not properly ended'. 


Does OutSystem have a different approach for SQL limit?


Kind regards,

Louise

Hi Louise,

You have to use TOP

DELETE TOP (1000) FROM YourEntityName
Solution

Hi Louise,

It depends on your underlying database. Assuming you use MSSQL Joao's answer is correct (but see below). If you have MySQL, you can use LIMIT. I'm not sure if Oracle supports TOP, if not use ROWNUM <= 1000.

Also note that if you do not specify an ORDER, the database may delete any 1000 rows as it sees fit (Microsoft mentions "a random selection", though I'm not sure how random that actually is). If you want to delete the first 1000, use ORDER BY Id (or whatever your Id column is called).

Solution

I think I made a wrong assumption. The error is indeed from an Oracle DB. 

In Oracle use ROWNUM <= 1000.

Hi João and Kilian,


Thank you all for the response. 


TOP is effective in my personal environment (I test complicated stuff here first before applying it to enterprise). And NUMROW is effective in our enterprise environment. 


Thank you again for the answers, it is resolved. :)


Kind regards,

Louise