Syntax error: missing expression (ORA-00936)

The SQL statement you posted has an extra comma. If you run the statement in SQL*Plus, it will throw the ORA-00936: missing expression and show you exactly where the error occurs

SQL> edWrote file afiedt.buf  1  INSERT INTO Services (service_id, service_name, service_facility)  2  SELECT 06, 'Rooms',  3  (SELECT facility_id, FROM Facilities WHERE facility_name = 'Boston')  4* FROM DualSQL> /(SELECT facility_id, FROM Facilities WHERE facility_name = 'Boston')                     *ERROR at line 3:ORA-00936: missing expression

If you remove the comma, the statement works

SQL> edWrote file afiedt.buf  1  INSERT INTO Services (service_id, service_name, service_facility)  2  SELECT 06, 'Rooms',  3  (SELECT facility_id FROM Facilities WHERE facility_name = 'Boston')  4* FROM DualSQL> /1 row created.

Note, however, that I would generally prefer Stefan's syntax where you are selecting from Facilities rather than selecting from dual with a scalar subquery.

Your insert statement should be:

INSERT INTO     Services     (        service_id,         service_name,         service_facility    ) SELECT     06,     'Rooms',     facility_id FROM     Facilities WHERE     facility_name = 'Hotel'

In it's current state the query i provided will add a service record for rooms for each facility with the name of Hotel.

And then add on a join to your 'Dual' table so that you get the correct number of inserts / are getting the correct facilities.