Using a Group By in an Update Statement

Posted on 2009-04-26
Last Modified: 2012-05-06
I'm trying to compile the statement attached below.  I keep getting an error "Incorrect syntax near the keyword 'group'"  Can i not use a group by with an udpate?
Update	a

	Set		a.[Name] =  upper(Max(b.ResortName)),

			a.Address1 = upper(Max(b.Address1)),

			a.Address2 = upper(Max(b.Address2)),

			a.City = upper(Max(b.City)),

			a.[State] = upper(Max(b.State)),

			a.PostalCode = upper(Max(b.PostalCode)),

			a.Country = upper(Max(b.Country)),

			a.Phone = b.Phone,

			a.Featured = b.Featured,

			a.DateModified = getdate(),

			a.ModifiedUserID = @UserID

	From	dbo.PackageImportStaging b join

			dbo.Resorts a on b.ExternalIDResort = a.ExternalID

	Where	b.Bad = 0 and

			b.ImportID = @ImportID

	group by b.ExternalIDResort

Open in new window

Question by:davidcahan
    LVL 142

    Accepted Solution

    you cannot do like that...
    Update  a
          Set a.[Name] =  upper(b.Max_ResortName)
            , a.Address1 = upper(b.Max_Address1)
            , a.Address2 = upper(b.Max_Address2)
            ... etc
            , a.DateModified = getdate()
            , a.ModifiedUserID = @UserID
          From dbo.Resorts a 
          join  ( SELECT b.ExternalIDResort
                       , Max(b.ResortName) Max_ResortName
                       , Max(b.Address1) Max_Address1
                        ... etc ...
                    from dbo.PackageImportStaging b 
                   Where b.Bad = 0 
                     and b.ImportID = @ImportID
                  group by b.ExternalIDResort
             on b.ExternalIDResort = a.ExternalID

    Open in new window


    Author Comment

    thanks...that's exactly what i ended up doing.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now