Skip to Main Content
  • Questions
  • Error with cursor syntax insert where not exists

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, ayah.

Asked: December 17, 2018 - 12:19 pm UTC

Last updated: December 17, 2018 - 5:05 pm UTC

Version: 11.2.0.2.0

Viewed 1000+ times

You Asked

i have a new table called jobs_new(job_id , job_title , min_salary , max_salary) and it have only one row with the job_id 'ST_MAN'

i want to make anonymous block to copy the other rows from jobs table except when the job_id is 'ST_MAN' because it is already exists in new_jobs table

declare
 cursor c2 is
 select * from jobs ;
 begin
 for c2_rec in c2
 loop
 insert into jobs_new(job_id , job_title , min_salary , max_salary)
 values (c2_rec.job_id , c2_rec.job_title , c2_rec.min_salary , c2_rec.max_salary)
 where not exists (select * from jobs_new where jobs_new.job_id = jobs.job_id);
 end loop;
 end;


this syntax gives me errors

and Chris said...

You can't use a where clause with single row inserts (insert ... values ...).

The good news is: you don't need a cursor at all!

You can do an insert ... select ... instead. Like:

insert into jobs_new (
  job_id,
  job_title,
  min_salary,
  max_salary
) 
select * from jobs
where not exists (
  select *
  from   jobs_new
  where  jobs_new.job_id   = jobs.job_id
);


Not only should this work, it'll be faster than the original cursor loop.

Remember: if you're doing DML (insert/update/delete) inside a loop, you're Doing It Wrong™.

Use one statement to process many rows. Not many executions processing one row!

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ayah, December 17, 2018 - 4:29 pm UTC

But i need it to deal with row by row because i have a trigger in new_jobs that puts sequence number to each new job_id if it is not replicated.
Does what you said work with this case ?
Chris Saxon
December 17, 2018 - 5:05 pm UTC

Triggers still fire when you do insert-select. If there's an error, you should change the trigger.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.