Oracle SQL - insert into select statement - error

Question

I created an insert statement, inside that insert statement I would like to pull data from another column in another table. I wrote the following script and get an error.

I wrote the actual select statement and it worked by itself, here is the script:

SELECT job_id 
FROM JOBS 
WHERE job_id IN ('AD_CMMS')

The problem occurs when I try to incorporate the insert into statement with the select statement, below is the complete script including the select statement:

INSERT INTO Employees
VALUES (242, 'Anouar', 'seljouki', '[email protected]', '0662777081', date19-May-12,
        SELECT job_id FROM JOBS WHERE job_id IN ('AD_CMMS'), 
        16000, NULL, NULL, NULL); 

When I run the script above, I get this error:

Error starting at line : 26 in command -
INSERT INTO Employees VALUES (242,'Anouar','seljouki','[email protected]','0662777081',date19-May-12, SELECT job_id from JOBS where job_id in('AD_CMMS'),16000,NULL,NULL,Null)
Error at Command Line : 28 Column : 1
Error report -
SQL Error: ORA-00936: missing expression
00936. 00000 - "missing expression"

Answer 1

The scalar subquery needs to be surrounded with parentheses. Also, the date literal syntax needs to be fixed.

So:

INSERT into Employees 
VALUES (
    242,
    'Anouar',
    'seljouki',
    '[email protected]',
    '0662777081',
    date '2012-05-19', 
    (SELECT job_id from JOBS where job_id = 'AD_CMMS'),
    16000,
    NULL,
    NULL,
    NULL
);

Beware that the subquery must not return more than one row, otherwise you would get an error.

You could also phrase this as an INSERT ... SELECT statement; this guarantees that the row will not be inserted if there is no match in JOBS:

INSERT into Employees 
SELECT 
    242,
    'Anouar',
    'seljouki',
    '[email protected]',
    '0662777081',
    date '2012-05-19', 
    job_id,
    16000,
    NULL,
    NULL,
    NULL
FROM JOBS 
WHERE job_id = 'AD_CMMS'

Side notes:

• if you need to stick the the current date format that is showed in your question, then use to_date('19-May-12', 'dd-mon-yy') instead.

• it is a good practice to enumerate the target columns for insert