CODEWITHSUNDEEP
sql-servert-sql
user1858332
user1858332

Reputation: 2075

Is it possible to use MAX in update statement using sql?

i am trying to use the MAX function in sql statement. Here is what i am trying to do: something like this:

UPDATE MainTable
        SET [Date] = GETDATE()

        where [ID] = Max

I know this is wrong specially where i put the where condition but cannot figure out how to use max and update in the same statement. thanks

Upvotes: 7

Views: 52003

Answers (3)

SQLMenace
SQLMenace

Reputation: 134923

One way

DECLARE @MaxID INT = (select MAX(id) FROM MainTable)

UPDATE MainTable
        SET [Date] = GETDATE()
        where [ID] = @MaxID

That is SQL 2008 syntax, in 2005 you need to do the declaraion and assignment of the variable in two steps

You could also use a common table expression

;WITH cte
AS (
SELECT TOP 1 * FROM MainTable
ORDER BY ID DESC

)

UPDATE cte SET [Date] = GETDATE()

Example you can run

CREATE TABLE testNow(id int)
INSERT testNow VALUES(1)
INSERT testNow VALUES(2)

;WITH cte
AS (
SELECT TOP 1 * FROM testNow
ORDER BY ID DESC

)

-- id with 2 will become 5
UPDATE cte SET ID = 5

SELECT * FROM testNow

Output

1
5

Upvotes: 6

Melanie
Melanie

Reputation: 3111

UPDATE MainTable
SET [Date] = GETDATE()
WHERE [ID] = (SELECT MAX(your column) FROM yourtable)

Upvotes: 3

zerkms
zerkms

Reputation: 254886

UPDATE MainTable
   SET [Date] = GETDATE()
 where [ID] = (SELECT MAX([ID]) FROM MainTable)

Upvotes: 26

Related Questions