Using SQL Query - How to insert a row in a table with some new values and some values from another table

Question

Example I have Table1 with 3 columns as col1, col2, col3. I have Table2 with 2 columns as id, col4.

Table1

col1	col2	col3
xyz	abc	12
qwe	rty	34

Table2

id	col4
14	asdf
15	oldvalue
15	oldvalue

I have to insert a row in Table1 such that -

1. Insert 'newvalue1' in col1, 'newvalue2' in col2.
2. In col3, insert value of id column from Table2 where col4 = 'oldvalue'.

My new row in table will be inserted and my output is -

Output-Table1

col1	col2	col3
xyz	abc	12
qwe	rty	34
newvalue1	newvalue2	15

Which the SQL query can achieve the above output?

I am using Oracle SQL developer.

INSERT INTO Table1(col1, col2, col3) values ('newvalue1', 'newvalue2', Select distinct id from Table2 where col4 = 'oldvalue');

getting error -> ORA-00936: missing expression

Answer 1

In Oracle, to insert into table baaed on some query result, you can use insert into table... select...from... statement, and the subquery should contains all the columns.

Here is the query:

insert into table1(col1,col2,col3)
    Select distinct 'newvalue1', 'newvalue2', id 
    from Table2 
    where col4 = 'oldvalue'

COL1	COL2	COL3
xyz	abc	13
qwe	rty	34
newvalue1	newvalue2	15