Inserting new rows in a table with out hard coding.

[Lavender]

HI,
I'm trying to insert a new row into a table without hardcoding the value. How do I do that. Is their an example someone can provide me. Thanks.

The problem goes something like this below.

empid is to be 1 greater than the largest existing empid
ename will be your Oracle userid
rank should be whichever rank is associated with the lowest-paid salesperson.
If there is more than one lowest-paid salesperson then select the minimum rank.
salary should be 10% more than the lowest-paid salesperson. If there is more than one lowest-paid salesperson then select the minimum salary.

[skhanal]

You have to get the result using SELECT and use that to insert

INSERT INTO table
SELECT query

[Lavender]

thanks, I'll try that.

[zahid_ali]

Try this one

insert into new_table
(select * from old_table)


I hope this works...

[stecal]

There are several things going on with this homework question/problem. The first has to do with using a sequence, more than likely adding a new empid one more than the current one is driven off of a sequence. The second has to do with using built-in system variables/values (keyword: user). The third involves ranking and selection of minimum, and the last is using an in-line change to a current value (set salary*1.1).

What does your attempt look like?

[hpaiss]

maybe something like the following will do:


create sequence xxx nocache;

insert into ttt (id,username, job,rank, sal)
select (xxx.nextval, user, 'salesperson',min(rank) over (partition by job ), min(sal) over (partition by job )
from ttt
where job = 'salesperson'