CODEWITHSUNDEEP
sqldatabase
user1988594
user1988594

Reputation: 45

SQL MIN() as subquery - "group function not allowed"

I'm trying to select a value which corresponds to the lowest number in another column, and then use it to SET in another table.

But it's telling me "group function is not allowed here"

is there any other way to accomplish this? 

UPDATE team 
SET OWNERID = (SELECT USERID
         FROM teamuser
         WHERE MIN(CREATETIME) 
         AND teamId = 5) 
WHERE teamId = 5

(Both team and teamuser tables have a column teamId).

Upvotes: 2

Views: 733

Answers (2)

shahkalpesh
shahkalpesh

Reputation: 33476

UPDATE team 
SET OWNERID = (SELECT USERID
         FROM TEAMUSER 
         WHERE CREATETIME = (SELECT MIN(CREATETIME) FROM TEAMUSER WHERE teamId = 5)
         AND teamId = 5) 
WHERE teamId = 5

Upvotes: 2

Gonzalo.-
Gonzalo.-

Reputation: 12672

you should do

UPDATE team 
SET OWNERID = (SELECT USERID
         FROM TEAMUSER 
         WHERE CREATETIME = (select min(CREATETIME) from TEAMUSER)
         AND teamId = 5
)
WHERE teamId = 5

You can't use agreggate functions in where clause, only you can use it on selects, or in Having clause, after group by

Upvotes: 2

Related Questions