Inserting without duplicating

5 pts.
Tags:
PL/SQL
I need to create a row and to ensure that the row I'm creating does not already exist. I'm doing this in a pl/sql program.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Is it possible to create a unique index on the table? if so, you could have your exception just ignore the duplicate error. Or you could do a select statement prior to the insert that would check for the existing row and bypass the insert statement if the record exists.

Example1:

–ignore duplicate value using of unique index
begin
insert into table (col1, col2…) values (value1, value2,…);
exception
when dup_val_on_index then –index will not allow a duplicate record
null; — or you could use Message to display that there was an attempt to add a duplicate row
end;

Example2:

–Check for duplicate
declare cnt number;
begin
select count(*) into cnt from table where col1 = ….;
if cnt = 0 then –no record exists so record can be inserted
insert into table (col1, col2, ….) values (value1, value2….);
end if; –you could put an else with a Message to display an attempt to add a duplicate row
end;

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following