Insert using a subquery as a target

Question

INSERT INTO (SELECT id,col1,col2,col3,col4 FROM table WHERE col1=1234) 
   VALUES(SEQ.NEXTVAL,2456,'qwew','12312','12312');

For the above query, I thought, I can only insert rows to table where the col1 has value of 1234. But, I was able to insert values other than 1234 for col1.

Question:

Why do we need the query like above? What is the real life scenario to use it?

Answer 1

It is basically a construct allowing for updateable Views. For a multi table scenario a INSERT can only be to one of the underlying tables. There has to be a One-One relationship between the View and the table being inserted into.

The query you have shown is a inline view extending the same concept.

Read here for more documentation

http://docs.oracle.com/cd/E17952_01/refman-5.1-en/view-updatability.html

Real life - is so as to be able to do this via View gives flexibility, ease. But more valuable inputs are welcome.

Answer 2

I wasn't aware you could do that, to be honest, but I suppose it is a by-product of the fact that you can insert into a view, and (SELECT id,col1,col2,col3,col4 FROM table WHERE col1=1234) is an in-line view.

As you imply, in the case of an INSERT there isn't much point, but it can be useful for an UPDATE or DELETE, typically where a join is involved:

update (select id, col1 from table1
        join   table2 on table2.col2 = table1.col2
        where  table2.col3 = 'X'
       )
set    col1 = 'Y';

This will only work if the column is considered updatable by Oracle, which requires that there is a foreign key from table1 to table2 in this example.

With "real" views you can make the restriction on an insert:

create view myview as SELECT id,col1,col2,col3,col4 FROM table WHERE col1=1234
WITH CHECK OPTION;

The WITH CHECK OPTION clause means that the WHERE clause of the view must not be violated by the new rows:

SQL> insert into myview (id, col1) values (SEQ.NEXTVAL, 9876);
                 *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

I don't think there is a way to do that with inline views though.