CODEWITHSUNDEEP
sql-server
ab11
ab11

Reputation: 20090

SQL Server, how to conditionally update multiple records?

My table has these columns

[ID] [bigint] IDENTITY(1,1) NOT NULL,
[PersonID] [bigint] NOT NULL,
[B] [bit] NULL

Given a list of PersonID values, I would like to update the table, setting the value of B for the most recent entry for each PersonID to 1.

The below script only updates a single record, but it seems something resembling it may work?

  UPDATE table
  SET B = 1
  WHERE PersonID in (<idlist>)
    AND ID IN (Select MAX(ID) FROM table WHERE PersonID in <idlist>)

Upvotes: 1

Views: 47

Answers (4)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112392

IN (Select MAX(ID) FROM ...) limits to a unique MAX(ID).

Group by PersonID in order to get the MAX(ID) for each Person.

UPDATE table
  SET B = 1
  WHERE
    ID IN (SELECT MAX(ID)
           FROM table
           WHERE PersonID in (<idlist>)
           GROUP BY PersonID)

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93724

If am not wrong this is what you are trying to achieve 

;with cte as
(
select row_number()over(partition by PersonID order by ID desc) as rn,*
from yourtable
Where PersonID in (<idlist>)
)
update cte set B=1
where Rn=1

Or use correlated sub-query

UPDATE A 
  SET A.B = 1
from table A 
  WHERE PersonID in (<idlist>)
  AND ID = (Select Top 1 ID FROM table B WHERE A.PersonID = B.PersonID 
            order by id desc)

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

Your initial script is very close. Just use correlated subquery:

UPDATE t
SET B = 1
FROM #tab t
WHERE t.PersonID in (<idlist>)
  AND ID IN (SELECT MAX(ID) FROM #tab t2 WHERE t.PersonID = t2.PersonID);

LiveDemo

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Assuming that id is unique, your subquery just needs a correlation clause:

 UPDATE table t
   SET B = 1
   WHERE t.PersonID in (<idlist>) AND
         ID IN (Select MAX(ID) FROM table t2 WHERE t2.PersonID = t.PersonId);

Alternatively, you could use group by, but that would probably be more expensive:

 UPDATE table t
   SET B = 1
   WHERE t.PersonID in (<idlist>) AND
         ID IN (Select MAX(ID)
                FROM table t2
                WHERE t2.PersonID in (<idlist>)
                GROUP BY t2.PersonId
               );

The use of row_number() with an updatable CTE is another reasonable approach.

Upvotes: 0

Related Questions