当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Find duplicate row record and update another column

问题描述:

I have the following table:

Destination Last Result

(555) 319-5170 Disconnect

(555) 319-5170 Duplicate

(555) 319-5170 Duplicate

(555) 261-5000 Duplicate

(555) 261-5000 Duplicate

(555) 261-7325 Busy

(555) 261-7325 Duplicate

I would like to find all duplicate Destination(Phone numbers) and update the 'Last Result' column for all matching phone numbers with a value that is not equal to 'Duplicate'. So, in the example table above for Destination((555) 319-5170), the result for all matching phone numbers(555) 319-5170) with the value 'Disconnect'. My query is below, but I am getting an error.

update Call

set [last result] = (

select [last result]

from Call

WHERE Destination in (select destination from Call group by destination having count(*)>1) and [Last result] != 'Duplicate' and [Last result] != 'No Phone #')

WHERE Destination in (select destination from Call group by destination having count(*)>1) and [Last result] != 'Duplicate' and [Last result] != 'No Phone #'

Destination Last Result - EXPECTED OUTPUT

(555) 319-5170 Disconnect

(555) 319-5170 Disconnect

(555) 319-5170 Disconnect

(555) 261-5000 Duplicate

(555) 261-5000 Duplicate

(555) 261-7325 Busy

(555) 261-7325 Busy

Thanks in advance for any assistance with a possible solution.

网友答案:

Your sub query is returning more than one result which is why you are getting an exception.

This query should work for you instead (if i've understood your criteria correctly):

UPDATE C
SET [Last Result] = 'Disconnect'
FROM Call C
INNER JOIN
        (
            SELECT Destination
            FROM Call
            WHERE [Last Result] NOT IN ('Duplicate','No Phone #')
            GROUP BY Destination
            HAVING COUNT(*) > 1
        ) Dup ON Dup.Destination = C.Destination
WHERE C.[Last Result] NOT IN ('Duplicate','No Phone #')
网友答案:

I am making a small change to JBond312's answer,

UPDATE C
SET [Last Result] = 'Disconnect'
FROM Call C
INNER JOIN
        (
            SELECT Destination
            FROM Call
            WHERE [Last Result] NOT IN ('Duplicate','No Phone #')
            GROUP BY Destination
            HAVING COUNT(Destination) > 1
        ) Dup ON Dup.Destination = C.Destination
WHERE C.[Last Result] NOT IN ('Duplicate','No Phone #')
分享给朋友:
您可能感兴趣的文章:
随机阅读: