CODEWITHSUNDEEP
sqloracle-database
Vincent Wu
Vincent Wu

Reputation: 1

Oracle SQL Developer combine using "WITH AS", "INSERT INTO", SELECT"

I am trying to figure out a way to do this.

I have a table with only read access, so let's call it existing_table. I want to have a reference table using "with as" statement and insert a new row to my reference table.

My code is: (pretend existing_table is ready for use)

INSERT INTO NEW_TABLE ( COLUMN_A, COLUMN_B)

VALUES (1, 'A')

WITH NEW_TABLE 

AS (SELECT * from EXISTING_TABLE)

SELECT * from NEW_TABLE

However, it doesn't work. Help please!!!!! "WITH" is where it gives me the error. if I move insert into statement after with as then "INSERT" is where it gives me the error.

My question is how can I use with/insert/select statement?

Upvotes: 0

Views: 291

Answers (1)

Himanshu
Himanshu

Reputation: 3970

Change the name of the identifier to some other name in WITH Clause as you have same name leading to ambiguity. Either way I guess you want like

   Create Table 
   NEW_TABLE AS SELECT *
   FROM EXISTING_TABLE;

   SELECT * FROM NEW_TABLE

Upvotes: 1

Related Questions