• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 617
  • Last Modified:

Using a Group By in an Update Statement

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

1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

davidcahanAuthor Commented:
thanks...that's exactly what i ended up doing.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now