1

I am trying to insert a resultSet of another select query in below query i am not sure where is my mistake.. getting SQL Error: ORA-00936: missing expression 00936. 00000 - "missing expression"

Here I am trying to insert a record 66,77 which are not in Departments table..

    INSERT INTO departments(DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID,LOCATION_ID)
      SELECT DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID (select * from 
     (SELECT 66 as DEPARTMENT_ID,'Administration' as DEPARTMENT_NAME, 200 as MANAGER_ID, 1700 as LOCATION_ID from dual
        Union all
      SELECT 77 as DEPARTMENT_ID,'Marketing' as DEPARTMENT_NAME, 201 as MANAGER_ID, 1800 as LOCATION_ID from dual)
          minus select * from departments 
      ) from dual;
vinod
  • 1,178
  • 4
  • 16
  • 42

1 Answers1

1

This part won't compile:

,LOCATION_ID (select *

This should work:

insert into departments(department_id, department_name, manager_id,location_id)

            select 66,'Administration',200,1700 from dual
union all   select 77,'Marketing'     ,201,1800 from dual
minus       select department_id, department_name, manager_id,location_id from departments
David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
  • I asked the same question for delete can plz look into it http://stackoverflow.com/questions/41087642/dynamic-delete-in-oracle – vinod Dec 13 '16 at 04:58