Re: Delete Only 50 rows from table

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????? -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche "I'm trying to see things your way, but I can't get my head up my ass" - JCH "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco Travel broadens a person. You look as if you have been all over the world. I went to the airport to check in and they asked what I did because I looked like a terrorist. I said I was a comedian. They said, "Say something funny then." I told them I had just graduated from flying school. -- Ahmed Ahmed http://i2.photobucket.com/albums/y41/Obnoxio/thinkIfoundtheproblem.jpg sending to informix-list

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