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)

We're not taking comments currently, so please try again later if you want to add 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.