CODEWITHSUNDEEP
sqldatabaseoracle-database
akrelj
akrelj

Reputation: 163

"ORA-00984: column not allowed here" when inserting with select statement

I would like to insert some data into a table. One field I would like to get from another table, so I'm using select statement inside. This is the code:

INSERT INTO t.table1 ( 
  id, 
  id_t2, 
  date_of_change 
) 
VALUES ( 
  t.table1_seq.nextval, 
  SELECT s.id_id_t2 from t.table2 s where s.something='something', 
  TO_DATE('02/05/2017 13:43:34','DD/MM/YYYY HH24:MI:SS') 
)

Although select statement is always returning only 1 field (1 row), I presume this is why I'm getting the error. How can I write INSERT statement with SELECT statement for just 1 field? Can it be done? If not, is there any other solution for this problem? Thank you.

Upvotes: 0

Views: 825

Answers (1)

Stefano Zanini
Stefano Zanini

Reputation: 5916

You can translate your whole insert statement into the form of

insert into table1 (fields)
select fields from table2

This will allow you to specify in your select some values from the source table and some constant values. Your resulting query would be

INSERT INTO t.table1 ( 
  id, 
  id_t2, 
  date_of_change 
) 
SELECT  t.table1_seq.nextval,
        s.id_id_t2,
        TO_DATE('02/05/2017 13:43:34','DD/MM/YYYY HH24:MI:SS') 
FROM    t.table2 s
WHERE   s.something='something'

Upvotes: 3

Related Questions