Cannot update the view or function 'xxxxxx' because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator also check VIEW WITH Check option and schema binding Methods

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

Also check VIEW WITH Check option and schema binding Methods

let see sample table

let create two view

CREATE VIEW PatientViewOptionA

as
SELECT PatientName,PatientAddress FROM Patients
WHERE (EXISTS (select PatientID from Prescriptions
WHERE (Patients.PatientID=PatientID)) AND EXISTS
(select PatientID from Visits WHERE (Patients.PatientID=PatientID)))

Create view patientviewOptionB

AS
select PatientName,PatientAddress
FROM (select Distinct PatientID,PatientName,PatientAddress
FROM Patients
WHERE (EXISTS(SELECT PrescriptionID,PrescriptionText,PatientID
FROM Prescriptions
where (Patients.PatientID=PatientID))
OR EXISTS (select VisitID,VisitReason,PatientID FROM Visits
where (Patients.PatientID=PatientID))))
PatientData

 If we update using OPTION B of view you get error due to it uses Distinct keyword in code

Update patientviewOptionB set PatientAddress='TUP' WHERE PatientName='Patient A'

Msg 4403, Level 16, State 1, Line 1

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

but if you use view option A You can able to update bcos it did nt use aggregates,pivot/unpivot,distinct keyword in code

Update PatientViewOptionA set PatientAddress='TUP' WHERE PatientName='Patient T'

VIEW WITH CHECK OPTION and WITH SCHEMABINDING  Methods

Another Example to need to update only particular records in table using view see below

You need to create view that allow users to modify only the customers records of customers in Maine

CREATE TABLE ViewTestCustomer(CustomerID int Primary Key,FirstName Varchar(50),LastName Varchar(50),Address1 Varchar(100),City Varchar(50),

State Varchar(25),ZipCode Char(10))


Insert into ViewTestCustomer
SELECT 7894,'Besant','Nagar','Chennai Beach','Chennai','TAMIL NADU','600025'
union all
SELECT 8695,'Maine','Falls','London falls','Maine','UK','100025'
union all
SELECT 4178,'SAINT','KULS','Australia street','Sydney','AUS','760025'

SELECT * FROM ViewTestCustomer

When you try to create view as below code you will get following error

Msg 1054, Level 15, State 6, Procedure vwMaine, Line 7
Syntax '*' is not allowed in schema-bound objects.

CREATE VIEW vwMaine

WITH SCHEMABINDING

AS

select * from ViewTestCustomer
where State='Maine'

WITH CHECK OPTION


Create unique CLUSTERED INDEX ix_CITY
ON vwMaine(City)

So you have to mention all column name write it separtely also you have to follow four part names

like dbo.schema name other wise you will get error as see below'

Msg 4512, Level 16, State 3, Procedure vwMaine, Line 7
Cannot schema bind view 'vwMaine' because name 'ViewTestCustomer' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

So after follow schema name rule kindly see below

 After Created Indexed view you can able to create unique clustered index on view as see below

 Thats it Thanks