Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005
    Posts
    24

    Unanswered: multiple insert problem

    hi there,

    i am wanting to insert a new row in a table for each ecr_apt_code. i can do this manually as expected but when i try to do it as follows it says i am violating a unique constraint.

    each of the colums is set to primary key. just confused that it will let me do it manually but not using this list

    INSERT INTO
    elig_criterias
    (ecr_hhts_hty_code,ecr_hhts_hrv_els_code,ecr_apt_c ode)
    SELECT
    'A120','ESCB',ecr_apt_code
    FROM
    elig_criterias
    WHERE
    ecr_apt_code IN ('2HSEANLX','2HSEARCX','2HSEBARX','2HSEBCKX')

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It seems that records which have "ecr_apt_code IN ('2HSEANLX','2HSEARCX','2HSEBARX','2HSEBCKX')" aren't unique; as you are inserting first two values (A120, ESCB) equal for all records, "ecr_apt_code" appears more than once in the table.

    If all columns are part of the unique key constraint, this should give distinct values (but it doesn't):
    Code:
    SELECT 'A120', 'ESCB', ecr_apt_code
    FROM elig_criterias
    WHERE ecr_apt_code IN ('2HSEANLX','2HSEARCX','2HSEBARX','2HSEBCKX');
    What to do? Well, you know your data. Sometimes (but not always) DISTINCT keyword helps. Try it:
    Code:
    SELECT DISTINCT 'A120', 'ESCB', ecr_apt_code
    FROM elig_criterias
    WHERE ecr_apt_code IN ('2HSEANLX','2HSEARCX','2HSEBARX','2HSEBCKX');
    If it is OK, include the DISTINCT keyword into the INSERT statement too. If not, we'll see what next.

  3. #3
    Join Date
    Oct 2005
    Posts
    24
    thanks little foot
    worked a treaT!

Posting Permissions

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