Removing Duplicates

[amit26]

I'm applying a sequence to a list of attributes sequentially, and if you have already incremented the sequence (made a primary key) for a attribute and then you come across the same attribute in the list, how do you stop it from incrementing the sequence because I have already created primary key for it.

For example takes the series of insert statements below:

INSERT INTO REC_COMPANY VALUES(company_seq.nextval,'Columbia');
INSERT INTO REC_COMPANY VALUES
(company_seq.nextval,'CBS Records');
INSERT INTO REC_COMPANY VALUES(company_seq.nextval,'RCA');
INSERT INTO REC_COMPANY VALUES(company_seq.nextval,'Virgin records');
INSERT INTO REC_COMPANY VALUES(company_seq.nextval,'BMG');
INSERT INTO REC_COMPANY VALUES(company_seq.nextval,'Polydor');
INSERT INTO REC_COMPANY VALUES(company_seq.nextval,'CBS');
INSERT INTO REC_COMPANY VALUES(company_seq.nextval,'Pickwick');
INSERT INTO REC_COMPANY VALUES(company_seq.nextval,'Polydor');
INSERT INTO REC_COMPANY VALUES(company_seq.nextval,'Atlantic');
INSERT INTO REC_COMPANY VALUES(company_seq.nextval,'Mega');
INSERT INTO REC_COMPANY VALUES(company_seq.nextval,'Grammy');
INSERT INTO REC_COMPANY VALUES(company_seq.nextval,'Musik Master');
INSERT INTO REC_COMPANY VALUES(company_seq.nextval,'Columbia');
INSERT INTO REC_COMPANY VALUES(company_seq.nextval,'Polydor');
INSERT INTO REC_COMPANY VALUES(company_seq.nextval,'WEA');
INSERT INTO REC_COMPANY VALUES(company_seq.nextval,'London');
INSERT INTO REC_COMPANY VALUES
(company_seq.nextval,'EMI');
INSERT INTO REC_COMPANY VALUES(company_seq.nextval,'Columbia');

In this example the record company 'columbia' comes up three times and therefore has three primary keys as i have to increment the sequence counter with every insert statement.

Can i use "if" and "else" to say that if the value exists then don't create a new sequence. How do i do this ?

I need some PL/SQL code to say something like

if the value in column two already exists
then do nothing
else
insert into table
end if

Any ideas on how to right such code? Any help would be much appreciated!

 

[avossel]

Why do you not only delete the obsolete inserts ?

Here are the code you need to do this:

CREATE OR REPLACE PROCEDURE commpany_add(cname IN VARCHAR2)
IS
cnt INTEGER;
BEGIN
SELECT COUNT(*) INTO cnt FROM REC_COMPANY r WHERE r.rec_comp_name = cname;
IF cnt = 0
THEN
INSERT INTO REC_COMPANY VALUES(company_seq.nextval, cname);
END IF;
END commpany_add;
/

EXEC commpany_add('Columbia')
EXEC commpany_add('CBS Records')
EXEC commpany_add('RCA')
EXEC commpany_add('Virgin records')
EXEC commpany_add('BMG')
EXEC commpany_add('Polydor')
EXEC commpany_add('CBS')
EXEC commpany_add('Pickwick')
EXEC commpany_add('Polydor')
EXEC commpany_add('Atlantic')
EXEC commpany_add('Mega')
EXEC commpany_add('Grammy')
EXEC commpany_add('Musik Master')
EXEC commpany_add('Columbia')
EXEC commpany_add('Polydor')
EXEC commpany_add('WEA')
EXEC commpany_add('London')
EXEC commpany_add('EMI')
EXEC commpany_add('Columbia')

COMMIT;