Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    26

    Unanswered: Filtering insertion by two fields

    Hi,

    I need to insert some registers in a table. These registers have three fileds: field1, field2 and field3. I don't want to insert a register if in the table already exists a row with the field2 and field3 combination of that register. And I don't want to declare these fields as key fields. How can I filter a "destination" table by two fields?
    I'll appreciate your answer

    Thanks


    F.
    Last edited by fmilano; 02-23-04 at 08:55.

  2. #2
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Filtering insertion by two fields

    Examples with simple data are always usefull

    Can you give us some ?

  3. #3
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    26

    Re: Filtering insertion by two fields

    An example with simple data,

    This the table1:

    create table table1(
    col1 int not null,
    col2 int not null,
    col3 int not null,
    constraint PK_table1 primary key (col2, col3)
    )

    This is my "insert" code:

    INSERT INTO table1
    SELECT table2.col1,table2.col2, table3.col3
    FROM table2, table3
    WHERE table2.col1 = table3.col1


    If there is already a row in table1 with a combination of table2.col2 and table3.col3, I want my insertion code not to insert the "new" register, the one that has that combination (to avoid repetitions by two fields). Since these are PK fields, I would like to avoid errors. I don't know if this can be done.

    Thanks,

    F.
    Last edited by fmilano; 02-23-04 at 09:30.

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Filtering insertion by two fields

    this would do

    INSERT INTO table1
    SELECT table2.col1, table2.col2, table3.col3
    FROM table2, table3
    WHERE table2.col1 = table3.col1 and
    not exists (

    Select table1.col1
    From table1, table2, table3
    WHERE table2.col1 = table3.col1 and
    table1.col2=table2.col2 and
    table1.col3=table3.col3)

  5. #5
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    26

    Re: Filtering insertion by two fields

    Thanks

    Now I have another doubt, because I thinks my simple code is not reflecting what I'm trying to do:


    INSERT INTO table1
    SELECT table2.col1, table2.col2, table1.col3
    FROM table2, table3
    WHERE table2.col1 = table3.col1 and
    not exists (

    Select table1.col1
    From table1, table2, table3
    WHERE table2.col1 = table3.col1 and
    table1.col2=table2.col2 and
    table1.col3=table3.col3)

    I changed the first SELECT, third column. Is this going to work?
    Last edited by fmilano; 02-23-04 at 10:28.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •