Tony.C Tony.C - 2 months ago 16
SQL Question

UPDATE Sql cannot have aggregate in set

Hi guys whats the work around to this error:

An aggregate may not appear in the set list of an UPDATE statement.

I've looked at a few examples but not really sure.

This is what I have now.

UPDATE UserAnswer
SET userId = (SELECT id FROM Users WHERE id=max(id))
WHERE userId = NULL


Thanks in advance!

Answer

Try using a variable, like this:

DECLARE @maxId INT;

SELECT @maxId = MAX(id)
FROM Users;

UPDATE UserAnswer
SET userId = @maxId
WHERE userId IS NULL;