Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    13

    Unanswered: T-SQL - Update prob

    I need to update different customer id's with one. This to eliminate the duplicates.
    After updating the record(s) with new customer Id, I can delete unwanted records
    But I have a PK defined on auct, cust ids which throws the error
    --error
    Server: Msg 2627, Level 14, State 1, Line 3
    Violation of PRIMARY KEY constraint 'pkx1'. Cannot insert duplicate key in object 'x1'.
    The statement has been terminated.

    --table creation script
    create table x1 ( auctid varchar(10) , custid varchar(10)constraint pkx1 primary key (auctid,custid))
    insert into x1 values ('aa','bb')
    insert into x1 values ('aa','cc')
    --update script
    declare @OrigCustomerId varchar(10)
    set @OrigCustomerId = 'ee'
    --update stmt1
    Update a Set custId = @OrigCustomerId FROM x1 a
    WHERE
    CustId IN ('bb','cc')
    and
    auctid not in (Select auctid FROM x1 WHERE custid = @OrigCustomerId)
    --delete stmt1
    Delete x1 where custid in ('bb','cc')

    How to solve this? or Is there any other alternative not using the cursors

  2. #2
    Join Date
    Oct 2003
    Location
    Manila, Philippines
    Posts
    20

    Re: T-SQL - Update prob

    Originally posted by ragav
    I need to update different customer id's with one. This to eliminate the duplicates.
    After updating the record(s) with new customer Id, I can delete unwanted records
    But I have a PK defined on auct, cust ids which throws the error
    --error
    Server: Msg 2627, Level 14, State 1, Line 3
    Violation of PRIMARY KEY constraint 'pkx1'. Cannot insert duplicate key in object 'x1'.
    The statement has been terminated.

    --table creation script
    create table x1 ( auctid varchar(10) , custid varchar(10)constraint pkx1 primary key (auctid,custid))
    insert into x1 values ('aa','bb')
    insert into x1 values ('aa','cc')
    --update script
    declare @OrigCustomerId varchar(10)
    set @OrigCustomerId = 'ee'
    --update stmt1
    Update a Set custId = @OrigCustomerId FROM x1 a
    WHERE
    CustId IN ('bb','cc')
    and
    auctid not in (Select auctid FROM x1 WHERE custid = @OrigCustomerId)
    --delete stmt1
    Delete x1 where custid in ('bb','cc')

    How to solve this? or Is there any other alternative not using the cursors

    First, you defined your table with two primary keys (auctid and custid)
    But then in your update query, you tried to update all custid to all 'ee' which will result like this when selecting the table

    auctid custid
    aa ee
    aa ee

    This would normally violates your primary constraints.

    Please refine your update.
    What do you really want to happen in your query? WHy you have to update records to same custid?


    -bernie

  3. #3
    Join Date
    Jan 2003
    Posts
    13

    Re: T-SQL - Update prob

    Thanks bernie,

    I need to delete the duplicate records in the sense, that both cust ids cc, dd are belonging to its original id ee
    Here original id ee , may or may not exists in the table
    If original id ee exists:
    auctid, custid
    aa, bb
    aa, cc
    aa, ee
    I can delete the other 2 records ( as i need to delete the duplicate recs)

    If original id ee does not exists, then
    auctid, custid
    aa, bb
    aa, cc
    I need to update one record with custid = ee and delete the other record

    How to do this in Update statement?, atleast not using the cursors

Posting Permissions

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