Thread: subquery

How to retrive first 5 records, after that update function to be used for those records(first 5 records) using subquery.
for 1 record update sal+200
for 2 record update sal+220
for 3 record update sal+250
for 4th record update sal+300
for 5th record update sal+400

it is possible execute with use of subqueries???

Please help me in this regard.

Code:

UPDATE tablename 
SET    sal   = sal + 200
WHERE  empno = ( SELECT empno 
                 FROM (SELECT rownum rn, num  
                       FROM tablename)
                 WHERE rn =1);

krishna anna,
chala thanks.
Update for 5 records i have to run this query 5 times ante kada anna.

Ravi
Srikalahasti.

As per your sample data you can't update it in a single statement.

Hi,

Guys did you checked the Code, Is this Code works?
In my knowledge it will fail.

below pl/sql block can update sal of first five records.

declare
cursor c1 is select rownum,a.* from emp a where rownum<6;
c1row c1%rowtype;
begin
open c1;
loop
fetch c1 into c1row;
exit when c1%notfound;
if c1row.rownum=1 then
update emp set sal=sal+200 where empno=c1row.empno;
elsif c1row.rownum=2 then
update emp set sal=sal+220 where empno=c1row.empno;
elsif c1row.rownum=3 then
update emp set sal=sal+250 where empno=c1row.empno;
elsif c1row.rownum=4 then
update emp set sal=sal+300 where empno=c1row.empno;
elsif c1row.rownum=5 then
update emp set sal=sal+400 where empno=c1row.empno;
end if;
end loop;
close c1;
end;
/