CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
Troy Reynolds
Troy Reynolds

Reputation: 68

Using where clause in Insert statement while in CURSOR

I am running this code and am getting this error ORA-00933 SQL command not properly ended

How would you write this code so this error does not exist? member_addresses_to_remove is a CURSOR. 

 FOR curr_element IN member_addresses_to_remove 
  LOOP
    INSERT INTO schema.tablename(ID,NAME, PHONE_NUMBER)
    VALUES (curr_element.ID, curr_element.NAME,curr_element.PHONE_NUMBER)
    WHERE NOT EXISTS (SELECT 1
                      FROM SCHEMA.OTHERTABLE OT
                      WHERE OT.ID = curr_element.ID);                        
  END LOOP;
COMMIT;

Upvotes: 0

Views: 985

Answers (2)

WW.
WW.

Reputation: 24271

Your syntax is incorrect because you are combining a values clause with a where clause.

Try this:

 FOR curr_element IN member_addresses_to_remove 
  LOOP
    INSERT INTO schema.tablename(ID,NAME, PHONE_NUMBER)
    SELECT curr_element.ID, curr_element.NAME,curr_element.PHONE_NUMBER
    FROM DUAL
    WHERE NOT EXISTS (SELECT 1
                      FROM SCHEMA.OTHERTABLE OT
                      WHERE OT.ID = curr_element.ID);                        
  END LOOP;
COMMIT;

Upvotes: 2

Ramki
Ramki

Reputation: 463

Insert statement syntax is wrong, pls check below example. 

INSERT INTO trg_tbl (col1, col2, col3)
SELECT col1,
       col2,
       col3
FROM src_tbl
WHERE col4 = myvale;

test SQL by executing single SQL statement in SQLPlus then add it procedure

Upvotes: 0

Related Questions