update table where there is duplicate records using sql

Question

i am trying to update a table but my problem is the target table has duplicate records so my update is failing for that reason. This is the error: attempt to update a target row with values from multiple join rows. I know when updating a table, we have to join unique keys but i cannot delete the duplicates from the table so i am looking for a work around for my situation. The CUSTOMERTABLE is the one that has the duplicates. Here is my query:

UPDATE CUSTOMERTABLE
SET SERVICE = 'BILLING'
FROM
(SELECT distinct(CUSTOMER_ID)AS ACCT_ID
       ,ED.CUSTOMER_NAME
       , ED.CUSTOMER_ADDRESS
  FROM CUSTOMER_RELATION ED, STG_CUSTOMER_REV TXN
  WHERE ED.CUSTOMER_ID = TXN.CUS_ID
 )AS X

WHERE X.ACCT_ID = CUSTOMERTABLE.ACCOUNT_NUMBER;

score 2 · Accepted Answer · answered Nov 03 '13 at 16:10

2

Try writing it with an IN clause:

UPDATE CUSTOMERTABLE
SET SERVICE = 'BILLING'
WHERE CUSTOMERTABLE.ACCOUNT_NUMBER IN
    (SELECT CUSTOMER_ID
     FROM CUSTOMER_RELATION ED
     JOIN STG_CUSTOMER_REV TXN ON ED.CUSTOMER_ID = TXN.CUS_ID)

answered Nov 03 '13 at 16:10

acfrancis

3,569
25
21

score 2 · Answer 2 · answered Nov 03 '13 at 16:11

2

Here is another option, which probably has a better performance compared to an IN solution if CUSTOMER_RELATION or STG_CUSTOMER_REV are large tables.

UPDATE C
   SET SERVICE = 'BILLING'
FROM CUSTOMERTABLE C
WHERE EXISTS (SELECT 1 
              FROM CUSTOMER_RELATION ED, STG_CUSTOMER_REV TXN
              WHERE ED.CUSTOMER_ID = TXN.CUS_ID AND CUSTOMER_ID = C.ACCOUNT_NUMBER);

answered Nov 03 '13 at 16:11

Wagner DosAnjos

6,304
1
15
29

1

That's interesting. Why do you say that has better performance? – acfrancis Nov 03 '13 at 22:52
Check the following link for a discussion on this subject. http://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance – Wagner DosAnjos Nov 04 '13 at 12:58
Fair point but don't forget the query optimizer might generate the same plan for both if it can determine they are logically equivalent based on the table definitions. – acfrancis Nov 04 '13 at 13:51
1

I agree there are other things to consider (indexes, stats, optimizer, etc). It's not a given it will be faster. – Wagner DosAnjos Nov 04 '13 at 14:50

score 0 · Answer 3 · answered Nov 03 '13 at 16:07

0

Try grouping on the CustomerId

UPDATE CUSTOMERTABLE
SET SERVICE = 'BILLING'
FROM
(SELECT distinct(CUSTOMER_ID)AS ACCT_ID
       ,ED.CUSTOMER_NAME
       , ED.CUSTOMER_ADDRESS
  FROM CUSTOMER_RELATION ED, STG_CUSTOMER_REV TXN
  WHERE ED.CUSTOMER_ID = TXN.CUS_ID
  GROUP BY ED.CUSTOMER_ID
 )AS X

WHERE X.ACCT_ID = CUSTOMERTABLE.ACCOUNT_NUMBER;

You need to make sure that your select return non duplicates. Try using that select without the update statement and check if the select cotains the duplicates you want to get rid off.

answered Nov 03 '13 at 16:07

Mad Dog Tannen

7,129
5
31
55

thanks Key, the subquery runs fine but the problem is this:CUSTOMERTABLE.ACCOUNT_NUMBER, the account_number in the customer table has duplicates – moe Nov 03 '13 at 16:09
@moe, if the account_number has duplicates then the update will update them all unles you add a anothere where clause distinguish them. Did you try acfrancis answer? – Mad Dog Tannen Nov 03 '13 at 16:13

update table where there is duplicate records using sql

3 Answers3