Re: Help!Delete only some rows

Josef Teepe wrote:
>
> Hello,
>
> i guess i have a very strange problem! An interface is sending data! I
> write them into a database (table1) with java! After six rows, i have to
> use the average command and write the datas of the 6 rows into another
> table (table2).
> The avg- and the insert- functions into the other table are working
> fine!
> Now my problem: How can i programm it that the avg- function is only
> using six rows and not more! And how can i delete the six rows after the
> average function and the insert command is done?
> Here is my SQL code:
> insert into table2 (column1, column2, column3, column4)
> select avg(column1),
> avg(column2),
> avg(column3),
> avg(column4)
> from diplom.table1
> Can i use the SQL language or must i do it in another way?
> I would be very happy about a solution!
>
> Thanks in advance!!
>
> Josef Teepe

Hi,

you should think about using the ROWNUM-Funktion. ROWNUM is an internal function that identifies rowids. Example :

select * from table1 where rownum = 10

will select the first 10 rows. Not more - eventualy less if there are not enough rows. But be careful : ROWNUM will say nothing about the order in which data will be selected. So, if you insert 6 rows in the first table and then make an avg-insert in table2 with a subselect on table1 using rownum you'll get the correct result. Next time you insert 6 rows and using the avg-insert into table 2 will give FALSE results. Why ? Because ORACLE selects the same result set as in the first time. Perhaps you should youse an additional field in table1 telling you which one has already been selected.

Hope, my comment will help you.

Bye,

Hans Pfeiffer
ORACLE Database Administrator Received on Thu May 14 1998 - 16:10:48 CDT