CODEWITHSUNDEEP
sqlsql-serverselectview
user1673665
user1673665

Reputation: 522

SQL error on UPDATE view with GROUP BY

This is the view

SELECT src.OfferAngebotsnummer AS OAngNr1, 
       SUM(src.Summe2) AS Summe,  
       CSDokument_1.OfferAngebotsnummer AS OAngNr2,  
       CSDokument_1.Auftragsvolumen
FROM (
     SELECT OfferAngebotsnummer, 
            ROUND(Angebotssumme, 2) AS Summe2
     FROM dbo.CSDokument
     WHERE (MANeu = 'AS400') AND 
           (Art = '3') AND 
           (DokumentTyp = '3')) AS src 
     INNER JOIN
           dbo.CSDokument AS CSDokument_1 ON 
           src.OfferAngebotsnummer = CSDokument_1.OfferAngebotsnummer
     GROUP BY src.OfferAngebotsnummer,
                    CSDokument_1.OfferAngebotsnummer, 
                    CSDokument_1.Auftragsvolumen

And this is the UPDATE statement

update UpdateAuftragsvolumenAngebot
set Auftragsvolumen = Summe
where Auftragsvolumen <> Summe

But I get an error that it's not allowed to use UPDATE on view with group by clause. 

Cannot update the view or function 'UpdateAuftragsvolumenAngebot' 
because it contains aggregates, or a DISTINCT or GROUP BY clause, 
or PIVOT or UNPIVOT operator.

How can I accomplish the UPDATE?

Upvotes: 1

Views: 1769

Answers (2)

Jesse
Jesse

Reputation: 873

I would suggest not using the view and just move it into a correlated sub-query like the below. I suggest that because as soon as you aggregate a view you cannot update the underlying tables.

update CSDokument
set Auftragsvolumen = Summe
from CSDokument
inner join 
    (
    SELECT OfferAngebotsnummer, 
            ROUND(Angebotssumme, 2) AS Summe2
     FROM dbo.CSDokument
     WHERE (MANeu = 'AS400') AND 
           (Art = '3') AND 
           (DokumentTyp = '3')) AS src 
     INNER JOIN
           dbo.CSDokument AS CSDokument_1 ON 
           src.OfferAngebotsnummer = CSDokument_1.OfferAngebotsnummer
     GROUP BY src.OfferAngebotsnummer,
                    CSDokument_1.OfferAngebotsnummer, 
                    CSDokument_1.Auftragsvolumen
    ) as s
    on s.OfferAngebotsnummer = CSDokument.OfferAngebotsnummer
where CSDokument.Auftragsvolumen <> s.Summe

Upvotes: 1

Andre Gompelman
Andre Gompelman

Reputation: 44

I believe you should use the group by in your sub-query and not at the join stage.

Upvotes: 0

Related Questions