ORA-00917: missing comma when insert not exist in oracle

2
votes

I have query like this :

INSERT INTO KONTAK (IDKONTAK, NAMA, NOHP, ALAMAT, GROUPKONTAK_FK) VALUES 
(SQ_IDKONTAK.NEXTVAL, 'ANDIKA PRATAMA', '+6285226202202', 'JPR', '' WHERE NOT EXISTS
(SELECT * FROM KONTAK WHERE NAMA = 'AMIN'))

I want insert data to KONTAK table based on the values where not exist in KONTAK NAMA='AMIN'.

When I ran it I got error :

ORA-00917: missing comma

Any suggestion?

2
sqloracleinsert
Ya. It has got problems inserting the 5th column value. Tell me what is the exact value you need in the 5th column? - Varun Rao
Firstly, I don't think that is legal syntax, but besides that, why are you using WHERE EXISTS when you aren't joining to anything? - codenheim
I temporarily deleted my answer. Can you better explain what you are trying to accomplish. Are you trying to do a conditional insert. As in insert a list of constant (harcoded) values only if a certain value doesn't exist already? How does 'AMIN' relate to the values you are inserting? - codenheim
@VarunRao when i give value to 5tn column it still missing comma - aminvincent
@mrjoltcola i will try for suggest below - aminvincent

2 Answers

1
votes

Use Where condition in insert with select statement;

try;

INSERT INTO KONTAK (IDKONTAK, NAMA, NOHP, ALAMAT, GROUPKONTAK_FK) 
select 
SQ_IDKONTAK.NEXTVAL, 'ANDIKA PRATAMA', '+6285226202202', 'JPR', '' 
from dual
WHERE NOT EXISTS (
    SELECT * FROM KONTAK WHERE NAMA = 'AMIN'
)
1
votes

If you are trying to do a conditional insert, then you can either use MERGE, or use something like this, assuming you want to insert 'ANDIKA PRATAMA' only if 'AMIN' is not already there (which doesn't make sense to me, but it seems to be your goal):

insert into table1(id1, col1, col2)
  select sequence1.nextval, 'VAL1', 'VAL2'
    from dual
    where not exists (select 1 from table1 where col1 = 'AMIN')

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm