Skip to Main Content

insert ignore rows

user11301181Apr 1 2014 — edited Apr 1 2014

Hi,

I'm trying insert in a table as follows:

1. I've three tables named

          - Inscriptions: this table has old rows with duplicates

          - competitors: this table has information about the teams in a competition

          - Winners: this table has information about the winner teams in a competition

     The table inscriptions includes rows with mistakes as duplicates winners at the same competition. In this case, the inserted rows, from that table inscriptions, into winners table must be the first row selected, but shouldn't      produce violation of primary or unique key.

2. the structure of the tables:

          create table inscriptions

          (

               club varchar(10) not null,

               country varchar(10) not null,

               section varchar(10) not null,

               category varchar(10) not null,

               sport varchar(10) not null,

               competition varchar(10) not null,

               edition varchar(10) not null,

               category_competiion varchar(10) not null,

               division varchar(10) not null,

               year number(4) not null

          );

          create table competitors

          (

                club varchar(10) not null,

               country varchar(10) not null,

               section varchar(10) not null,

               category varchar(10) not null,

               sport varchar(10) not null,

               competition varchar(10) not null,

               edition varchar(10) not null,

               category_competiion varchar(10) not null,

               division varchar(10) not null,

              

               constraint pk_competitors primary key (club,country,sport,competition,edition, category_competition,division)

          );

          create table winners

          (

               club varchar(10) not null,

               country varchar(10) not null

               category varchar(10) not null,

               sport varchar(10) not null,

               competition varchar(10) not null,

               edition varchar(10) not null,

               category_competiion varchar(10) not null,

               division varchar(10) not null,

               year number(4) not null,

               constraint pk_winners primary_key (sport,competition,edition,category_competition,division)

               constraint fk_winners_compettitors foreign key (club,country,sport,competiton,edition,category_competiton,division) references competitiors (club,country,sport,competiton,edition,category_competiton,division) ;

          );

The tables inscriptions and competitors have registers. The first one, as commented  above, has invalid rows for example the diferent clubes win the same competition.

The insert instruction into winners table must verify the foreign key and not introduce the rows include different winner clubes from different countries at the same competition. For example:

    

     club          country               category                    sport               competition               edition          category_competition          division          year    

club A                Spain                   1st                         Football                    LPF                         XXIV ed               junior                                        1st               2013

club B               UK                         1st                         Football                    LPF                         XXIV ed               junior                                        1st               2013

club C                Spain                   1st                         Football                    LPF                         XXIV ed               junior                                        1st               2014

In this example only insert "club C.... " row.

The insert instruction I've tried is:

insert into winners(club,country,category,sport,competition,edition,category_competition,division,year)

  select distinct (club,country,category,sport,competition,edition,category_competition,division,year from inscriptions

  where exists

(

  select distinct club,country,category,sport,competition,edition,category_competition,division from participantes

  );

  

but there is a error message: ORA-00001: unique constraint pk_winners violated.

Please, help me, I'm crazy with this.

Thanks in advance.

Best regards.

Comments
Post Details
Added on Apr 1 2014
3 comments
546 views