Delete the first 100 rows .. ?
The poster asked how to delete the first 100 rows of a table, since SELECT FIRST 100 works but DELETE FIRST 100 isn't supported. Respondents pointed out that "first 100" is meaningless without an ORDER BY — you'd just delete 100 arbitrary rows — and urged him to state the real requirement (e.g. delete rows older than a date). The suggested workaround was two-step: select the primary keys (or rowids) of the first 100 rows into a temp table or application structure, then delete matching rows via a WHERE ... IN clause. No further explanation from the poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
the command for select the first 100 rows is:
SELECT FIRST 100 * FROM tab ...but the command
DELETE FIRST 100 FROM tab ..
is not possible, and informix docu:
The FIRST option is not valid in any of the following contexts:
a.. In the definition of a view
b.. In nested SELECT statements
c.. In subqueries
d.. In a singleton SELECT (where max = 1) within an SPL routine
e.. Where embedded SELECT statements are used as expressions
What is the right command for delete the first 100 rows?
thanks
Michael Andreas wrote
> Hi,
>
> the command for select the first 100 rows is:
> SELECT FIRST 100 * FROM tab ...> but the command
> DELETE FIRST 100 FROM tab ..
> is not possible, and informix docu:
>
> The FIRST option is not valid in any of the following contexts:
> a.. In the definition of a view
> b.. In nested SELECT statements
> c.. In subqueries
> d.. In a singleton SELECT (where max = 1) within an SPL routine
> e.. Where embedded SELECT statements are used as expressions
> What is the right command for delete the first 100 rows?
>
I'd do it in two steps:
- Read in the first 100 rows
- determine their primary key , store these values in an internal
table / structure in your application and then use this to
populate the WHERE clause for your DELETE statement.
Michael Andreas said: > What is the right command for delete the first 100 rows? Why? -- Bye now, Obnoxio "... no bill is required as no value was provided." -- Christine Normile -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
> > Why? > Why not?
Michael Andreas said: > >> >> Why? >> > > Why not? Because it's a nonsensical idea. -- Bye now, Obnoxio "... no bill is required as no value was provided." -- Christine Normile -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
effectively all you will do is delete 100 random rows surely you want to delete rows with a particular where clause Those before a certain date those with an id < x etc? if this is truely what you want to do then select the primary key of the first hundered rows into a temp table then run a delete to delete each of the associated rows from the temp table in the master table
Michael Andreas wrote:
> Hi,
>
> the command for select the first 100 rows is:
> SELECT FIRST 100 * FROM tab ...> but the command
> DELETE FIRST 100 FROM tab ..
> is not possible, and informix docu:
>
> The FIRST option is not valid in any of the following contexts:
> a.. In the definition of a view
> b.. In nested SELECT statements
> c.. In subqueries
> d.. In a singleton SELECT (where max = 1) within an SPL routine
> e.. Where embedded SELECT statements are used as expressions
> What is the right command for delete the first 100 rows?
8^(
Michael,
This post falls into one of our biggest pet peaves. Folk who determine a
solution to a problem but can't make it work. Instead of posting the
problem, possibly with the solutions they've already attempted, and asking
us how else they might solve it, they post the one failed solution and ask
us how to make that one solution work.
PPPPPPPPPPPPPPPPPLLLLLLLLLLLLLLLLLEEEEEEEEEEEEEEAAAAAAAAAAAASSSSSSSSSSEEEEEEEEE
post the problem and we'll be happy to help solve it with you.
Of course, if this is purely an intellectual exercise:
select first 100 rowid as ids from mytable WHERE ... into temp fred;
delete from fred where rowid in (select ids from fred);
;-)
Art S. Kagel
"Michael Andreas" <andy@dachrinne.de> wrote in message news:ejk2ds$re1$1@online.de... > >> >> Why? >> > > Why not? Because it's meaningless. You might as well say "I want to delete the 100 rows that are most yellow".
On Fri, 2006-11-17 at 15:16 +0000, Neil Truby wrote: > "Michael Andreas" <andy@dachrinne.de> wrote in message > news:ejk2ds$re1$1@online.de... > > > >> > >> Why? > >> > > > > Why not? > > Because it's meaningless. > You might as well say "I want to delete the 100 rows that are most yellow". It's meaningless unless DELETE supports ORDER BY. Who wants to bet that that'll be Michael's next request? ;-) -Carsten