Insert not getting compiled.

Question

Why this insert is not getting compiled :-

INSERT  INTO dbo.UserGroupsToUsers
                        ( UserID ,
                          LastUpdated ,
                          ID ,
                          UserGroupID 
                        )
                        SELECT  @MergeToUserID ,
                                GETDATE() ,
                                MAX(ID) + 1 ,
                                UserGroupID
                        FROM    dbo.UserGroupsToUsers
                        WHERE   UserID = @MergeFromUserID

Error: Column 'dbo.UserGroupsToUsers.UserGroupID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Answer 1

You should make your ID field into an IDENTITY (autoincrementing field) and omit it from the query.

If you can't change the database you could try this:

INSERT  INTO dbo.UserGroupsToUsers
                ( UserID ,
                  LastUpdated ,
                  ID ,
                  UserGroupID 
                )
                SELECT  @MergeToUserID ,
                        GETDATE() ,
                        (SELECT MAX(ID) + 1 FROM dbo.UserGroupsToUsers),
                        UserGroupID
                FROM    dbo.UserGroupsToUsers
                WHERE   UserID = @MergeFromUserID

Important note: This assumes that only one row will be returned. The insert will fail by design if your subquery returns more than one row and ID is a primary key or has a unique constraint.

Answer 2

MAX(ID) is an aggregate function. Like all aggregate functions, it requires grouping on the rest of the fields. After WHERE clause, add a group by clause on all the rest of the fields (aliases).

This is general, but for this case use Mark Byers solution.

Another idea is to not do MAX(), but rather like this:

INSERT  INTO dbo.UserGroupsToUsers
                        ( UserID ,
                          LastUpdated ,
                          ID ,
                          UserGroupID 
                        )
                        SELECT TOP 1 @MergeToUserID ,
                                GETDATE() ,
                                ID +1,
                                (SELECT UserGroupID FROM dbo.UserGroupsToUsers WHERE   UserID = @MergeFromUserID)
                        FROM    dbo.UserGroupsToUsers
                        ORDER BY ID DESC

Answer 3

You use a MAX(ID), this means the query uses grouping. But you do not use a aggregate function on UserGroupID, so SQL does not know what value to pick from the (possible) values.

Probably there is only one value, and you know that, but the SQL compiler does not know that.

Better to wrap UserGroupID also in a MAX, MIN, or other aggregate function.