Re: Delete Only 50 rows from table

Obnoxio The Chav wrote: > jayloub said: > >>It would be done in pure SQL. >>In Sql Server you can set a rowcount - and that will be the maximum >>number of rows deleted at one time. >> >>I am looking for something similar. > > > Why? Why? WHY????? > *Shudder* rowcount is one of the more evil crimes against SQL I have seen. How does it work when there is a join or a subselect. Will any fetch form a table be cut short? What about returns from table-functions? How does it affect INSTEAD OF triggers? This work can be done in pure SQL btw. Here is what I would type in another RDBMS without messing up the language: DELETE FROM (SELECT * FROM T ORDER BY date FETCH FIRST 500 ROWS ONLY) AS T; Req: ORDER BY in subselect and some sort of TOP, FETCH FIRST, etc logic. or with OLAP and views, SQL 1999 (I think OLAP is in '99) compliant: CREATE VIEW v AS SELECT ROW_NUMBER() OVER (ORDER BY date) AS rn FROM T; DELETE FROM v WHERE rn <= 500; Note to OTC: To remove the top-n-rows is a typical opeation on a table serving as a queue. It's usually a destructive read. I do follow the why.... Cheers Serge -- Serge Rielau DB2 SQL Compiler Development IBM Toronto Lab