SQL Update one of duplicate-CodePudding

I need help. I need to do an update on a database, but I have a problem. I have duplicates that have the same person id, but different product id. And now I need to change one of the duplicates, the one with the lower product id number, for each person id to inactive (false).

I can't manually list each product id to deactivate because there are several thousand records. Does anyone know how to do an update only for those records with lower product id to change their value to false, and leave True in those with higher id? It wants to update only the data that was created by the user "user" and has the code "123"

My tables:

Person ID | Product ID| active

123 | 16282 | T

123 | 12345 | T ( it must be false)

124 | 12340 | T

124 | 10000 | T ( it must be false)

198 | 11111 | T ( it must be false)

198 | 12000 | T

CodePudding user response：

We can use an update with exists logic:

UPDATE yourTable t1
SET active = CASE WHEN active = 'T' THEN 'F' ELSE 'T' END
WHERE EXISTS (
    SELECT 1
    FROM yourTable t2
    WHERE t2.PersonID = t1.PersonID   AND
          t2.ProductID > t1.ProductID AND
          t2.active = t1.active AND
          t2.user = 'user' AND t2.code = '123'
) AND user = 'user' and code = '123';

CodePudding user response：

Can also use a find first product for each person

update YourTable t  
inner join (select PersonId 
                 , min(productid) as FirstProductId 
              from YourTable
            group by PersonId) s
  on cast(t.PersonId as varchar(20)) || cast(t.ProductId as varchar(20)) 
      = cast(s.PersonId as varchar(20)) || cast(s.FirstProductId as varchar(20))
set Active = 'F'