What is the Purpose of Subquery in Insert?

Ed Prochak · *nix forums Guru Wannabe Joined: 17 Mar 2005 Posts: 271

Maybe the point is to be obscure?

Actually, it is an in-line view.

If the PK is just the employee_id, then this is definitely obscure.
It's obvious the department_id is never assigned a value (unless there
is some behind the scenes trigger).

Hmm, is this some way of avoiding a trigger on the employee table?

1 create table emp (
2 emp_id number PRIMARY KEY,
3 emp_nm varchar(20),
4 dept_id number ,
5* other varchar(10) )
SQL> /

Table created.

SQL> insert into emp values (1, 'adam', 50,'first');

1 row created.

SQL> insert into emp values (2, 'brent', 30,'second');

1 row created.

SQL> insert into emp values (3, 'cary',10, NULL);

1 row created.

SQL> create or replace trigger emp_in before insert on emp
2 for each row
3 begin
4 :new.dept_id := 50;
5 end;
6 /

Trigger created.

SQL> select emp_id , emp_nm from emp
2 where dept_id=50;

EMP_ID EMP_NM
---------- --------------------
1 adam

SQL> select * from emp;

EMP_ID EMP_NM DEPT_ID OTHER
---------- -------------------- ---------- ----------
1 adam 50 first
2 brent 30 second
3 cary 10

SQL> insert into ( select emp_id , emp_nm from emp
2 where dept_id=50 )
3 VALUES ( 9999, 'peter' );

1 row created.

SQL> select * from emp;

EMP_ID EMP_NM DEPT_ID OTHER
---------- -------------------- ---------- ----------
1 adam 50 first
2 brent 30 second
3 cary 10
9999 peter 50

SQL> insert into ( select emp_id , emp_nm from emp
2 where dept_id=50 )
3 VALUES ( 9999, 'peter' );

1 row created.

SQL> select * from emp;

EMP_ID EMP_NM DEPT_ID OTHER
---------- -------------------- ---------- ----------
1 adam 50 first
2 brent 30 second
3 cary 10
9999 peter 50

SQL>insert into ( select emp_id , emp_nm from emp
2 where dept_id=50 )
3 VALUES ( 9999, 'peter' );

1 row created.

SQL> select * from emp;

EMP_ID EMP_NM DEPT_ID OTHER
---------- -------------------- ---------- ----------
1 adam 50 first
2 brent 30 second
3 cary 10
9999 peter 50

SQL>insert into ( select emp_id , emp_nm from emp
2 where dept_id=50 )
3 VALUES ( 9999, 'peter' );

1 row created.

SQL> select * from emp;

EMP_ID EMP_NM DEPT_ID OTHER
---------- -------------------- ---------- ----------
1 adam 50 first
2 brent 30 second
3 cary 10
9999 peter 50

SQL>

Nope, the trigger still fires. Might be a job-security-thru-obscurity
ploy? I see no reason to use that versus the simple:
insert into employee VALUES(9999,'peter');

HTH,
ed

Anurag Varma · *nix forums Guru Wannabe Joined: 25 Mar 2005 Posts: 194

Although by the looks of it ... its a poor example.
However, its trying to introduce to you the insert into (view) ...
syntax.

In the above example, the view is a dynamic view.
Probably later on its trying to introduce to you the "with check
option" etc. syntax.

So the clue for you is probably to ... read on! :)

Anurag

Den · *nix forums beginner Joined: 12 Jun 2005 Posts: 6

This statement is found in oracle's training material:

INSERT INTO (SELECT employee_id, last_name
FROM employee
WHERE department_id=50)
VALUES(9999,'peter');

The above subquery is interpreted as the list columns to be inserted with
values. But what is the point of having this syntax? And why bother
putting in a WHERE clause? Isnt it good enough to list just the column names
instead of a doing a subquery which is nothing more than indicating what
columns are involved??

Google