We help IT Professionals succeed at work.

Update Table Set Where MAX(ID)?

pointeman
pointeman asked
on
The first code works fine, just like to shorten a bit.

[Working code]
Declare @maxid As int;
Set @maxid = (Select MAX(ID) From Table1);
Update Table1 Set IsAlert = 1 Where ID = @maxid

[Short code attempt, not working]
Update Table1 Set IsAlert = 1 Where (Select MAX(ID) From Table1) != null)

Help!
Comment
Watch Question

SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
<knee-jerk answer>

UPDATE Table1
SET IsAlert = 1
WHERE ID IN (SELECT Max(ID) FROM Table1)

Author

Commented:
Ahh, I also came up with code, only diff is '=' vs 'IN'

Update Table1 Set IsAlert = 1 Where ID = (Select MAX(ID) From Table1)
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>only diff is '=' vs 'IN'
Performance-wise this is equivalent.  If the IN ( SELECT ...)  is a large number of rows then you'd want to JOIN it.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Thanks for the grade.  Good luck with your project.  -Jim