Update Only Rows with Shared/Similar Values

问题:

OK - I have a simple table - below - what I am trying to do is to identify only those rows that have a shared value - so anything that has a shared "apc" value - DO x else Do Y

CREATE TABLE #test (hcpcs varchar(10), apc varchar(100), rate money) 

INSERT INTO #test (hcpcs, apc)
SELECT '97110', '8009'
UNION ALL
SELECT '71020', '8009'
UNION ALL
SELECT '85025', '8006'

So - from the above - all those rows that share "8009" - I will gram those rows for an update - they will share the same "rate". Row with "8006" will not be a part of that update

回答1:

You want:

WHERE EXISTS (SELECT *
     FROM #test t2
     WHERE t2.apc = #test.apc
     AND t2.hcpcs != #test.hcpcs)

Make sure apc is indexed so that this can be done effectively.

回答2:

select 
    distinct
    apc,
    countAPC = count(apc) over(partition by apc)
into
    #testShared
from 
    #test

update #test 
set 
    rate = 2
where
    apc = (select apc from #testShared where countAPC > 1)

回答3:

You can use a CTE with Count(*) over ( Partition By apc):

WITH CTE AS(
  SELECT t.hcpcs, t.apc, t.rate,
         count_apc = Count(*) over ( Partition By t.apc)
  FROM Test t
)
SELECT hcpcs, apc, rate
FROM cte 
WHERE count_apc > 1

Demo

You can simply replace the Select with an Update CTE, actually you'll update the table Test.

Demo

回答4:

You can try like below, where the inner query will get you the apc which has been repeated. group by apc will group the rows by apc and get the apc which has more than 1 occurrence.

update #test
set rate = someval
where apc in ( 
select apc from #test
group by apc
having count(*) > 1
)