IS VIEWS UPDATABLE ?? Read the article to know how interesting view is...

-- A NICE AND INTELLIGENT QUESTION ABOUT VIEWS---------

--QUESTION: IS VIEWS UPDATABLE ?

--VIEW IS NOTHING BUT A VIRTUAL TABLE WHICH CONTAINS DATA FROM DIFFERENT BASE TABLE...LETS DEMONSTRATE WITH A NICE EXAMPLE.

--LETS CREATE TWO TABLES 

1)HCL 

2)EMPLOYEE

CREATE TABLE HCL

(

[HCL DEPARTMENT] INT PRIMARY KEY NOT NULL,

[HCL OFFICE] VARCHAR(100)

)

CREATE TABLE EMPLOYEE

(

[HCL DEP] INT FOREIGN KEY REFERENCES HCL([HCL DEPARTMENT]),

SAPID INT,

NAME VARCHAR(100)

)

INSERT INTO HCL VALUES(1,'HCL NOIDA')

INSERT INTO HCL VALUES(2,'HCL CHENNAI')

INSERT INTO HCL VALUES(3,'HCL BANGALORE')

SELECT * FROM HCL

INSERT INTO EMPLOYEE VALUES(1,514,'ANURAG')

INSERT INTO EMPLOYEE VALUES(2,325,'SIBA')

INSERT INTO EMPLOYEE VALUES(3,575,'MUKUA')

INSERT INTO EMPLOYEE VALUES(4,777,'ABHI')

--NOW YOU CANNOT INSERT THE 4TH ROW,,,AS YOU ARE AWARE THAT IN INSERT KEY CONFLICTED WITH FOREIGN KEY CONSTRAINT.... WE DONT HAVE 4[HCL DEPARTMENT] IN HCL TABLE...SO WE CANNOT INSERT IN EMPLOYEE TABLE WITH DEPARTEMENT AS 4 WHICH IS A FOREIGN KEY ,,,REFRING TO PRIMARY KEY IN HCL TABLE..

SELECT * FROM EMPLOYEE

--TILL NOW CLEAR RIGHT ?? IF YES THEN PROCEED..

--LETS CREATE A VIEW..

CREATE VIEW VIEWANURAG

AS

SELECT E.[HCL DEP],E.NAME,E.SAPID,H.[HCL DEPARTMENT],H.[HCL OFFICE] FROM EMPLOYEE E

JOIN HCL H ON E.[HCL DEP]=H.[HCL DEPARTMENT]

SELECT * FROM VIEWANURAG

--NOW LETS INSERT A RECORD INTO THE VIEW..

--NOW INSERT RECORDS INTO VIEW---

INSERT INTO VIEWANURAG VALUES(3,'ABHI',777,3,'HCL KOLKOTA')

--YOU GET ERROR

--View or function 'VIEWANURAG' is not updatable because the modification affects multiple base tables...

--NOW INSERT RECORD BASE TABLE WISE…MEANS INSERT FIELDS OF ONLY ONE BASE TABLE…

--NOW LETS INSERT ONLY ONE TABLE i.e HCL..

SELECT * FROM VIEWANURAG

INSERT INTO VIEWANURAG ([HCL DEPARTMENT],[HCL OFFICE]) VALUES(3,'HCL KOLKOTA')

--OOPS WE HAVE MADE IT AS PRIMARY KEY,,,SO WE CANT INSERT 3...LETS MAKE THE HCL KOLKOTA AS 4

INSERT INTO VIEWANURAG ([HCL DEPARTMENT],[HCL OFFICE]) VALUES(4,'HCL KOLKOTA')—IT WORKS WE ARE ABLE TO INSERT

SELECT * FROM VIEWANURAG

SELECT * FROM HCL

--NOW THE ABOVE TWO SELECT STATEMENT,,,YOU WONT FIND ANY CHANGES IN VIEWANURAG,,,,BUT IF YOU LOOK THE BASE PHYSICAL TABLE HCL

--YOU SEE THAT THE RECORD IS INSERTED,,,

--NOW INSERT THE OTHER PART IN VIEW,,,

INSERT INTO VIEWANURAG ([HCL DEP],NAME,SAPID) VALUES(4,'ABHI',777) –IT WORKS TOO..WE ARE ABLE TO INSERT

SELECT * FROM VIEWANURAG

--COOL,,, NOW WE ARE ABLE TO SEE IN VIEW THE CHANGES...

--NOW LETS SEE THE BASE TABLE

SELECT * FROM EMPLOYEE

--PRETTY COOL HERE AS WELL,,,WE ARE ABLE TO SEE THE CHANGES HERE.....

---------<<<<<SO ONE THING WE HAVE SEEN HERE THAT ,,,YOU CAN ONLY INSERT COLUMNS OF VIEW IF THEY COME FROM SAME BASE TABLE..IF YOUR VIEW IS USING MULTIPLE BASE TABLES, YOU CANNOT INSERT MULTIPLE COLUMNS FROM MULTIPLE BASE TABLES,,,,

NOW UPDATE THE VIEW AND SEE WHETHER IT WORKS OR NOT

--<<BUT WAIT WE ARE NOT FINISHED YET>> LETS TRY THE UPDATE STATEMENT....WE WILL UPDATE THE VIEW...MEANS WE WILL UPDATE ONE COLUMN WHICH IS COMING FROM BASE TABLE HCL...USING BASE TABLE EMPLOYEE

UPDATE VIEWANURAG SET SAPID=888 WHERE [HCL OFFICE]='HCL BANGALORE'

UPDATE VIEWANURAG SET [HCL OFFICE]='HCL PUNE' WHERE [HCL DEP]=4

SELECT * FROM VIEWANURAG

--THIS WORKS...

SELECT * FROM HCL

SELECT * FROM EMPLOYEE

--AND THE BASE TABLE IS ALSO UPDATED...SO IT MEANS THE UPDATE IS WORKING FINE....BUT WAIT WE ARE UPDATING ONLY ONE BASE TABLE IN VIEW.... LETS TRY TO UPDATE BOTH BASE TABLE..LETS SEE

UPDATE VIEWANURAG SET SAPID=888,[HCL OFFICE]='HCL SECTOR 125' WHERE [HCL DEP]=4--

OOPS WE GET ERROR..View or function 'VIEWANURAG' is not updatable because the modification affects multiple base tables.

--WE ARE TRYING TO UPDATE SAPID WHICH IS COMING FROM EMPLOYEE TABLE AND [HCL OFFICE] WHICH IS COMING FROM HCL TABLE..WE CANT DO THAT.....

             <<<<<<<ONE MORE SCENARIO>>>>>>>>>>>>

SELECT * FROM EMPLOYEE

--LETS INSERT SOME RECORDS IN EMPLOYEE TABLE

INSERT INTO EMPLOYEE VALUES(3,243,'BIKASH')

INSERT INTO EMPLOYEE VALUES(3,711,'AKASH')

select * from employee

SELECT * FROM VIEWANURAG

--SO THE FIRST THREE COLUMNS OF VIEWANURAG IS COMING FROM EMPLOYEE TABLE

--NEXT TWO COLUMNS IS COMING FROM HCL TABLE.

==NOW LETS TRY TO UPDATE THE VIEWANURAG LIKE : UPDATE THE HCL OFFICE OF MUKUA TO [HCL BBSR]

--REQUIRED OUTPUT IS SOMETHING LIKE THIS...

 [HCL DEP]   [NAME]      [SAPID]     [HCL DEPARTMENT]    [HCL OFFICE]

--    1      ANURAG      514               1              HCL NOIDA

--    2      SIBA        325               2              HCL CHENNAI

--    3      MUKUA       888               3              HCL BBSR

--    4      BISWA       777               4              HCL PUNE

--    3      BIKASH      243               3              HCL BANGALORE

--    3      AKASH       711               3              HCL BANGALORE

--LETS UPDATE... NOW WE ARE UPDATING ONLY ONE BASE TABLE SO IT WILL ALLOW US....

UPDATE VIEWANURAG SET [HCL OFFICE]='HCL BBSR' WHERE NAME='MUKUA'

SELECT * FROM VIEWANURAG

--O/P

--BUT WAIT SEE THAT BIKASH AND AND AKASH THEY BELONGED TO HCL BANGALORE ...HOW THEY ARE SHOWING NOW HCL BBSR...STRANGE ISN'T IT.....TO EXPLORE MORE THINGS LET'S SEE OUR HCL TABLE...

SELECT * FROM HCL--OOPS HCL BANGALORE IS MISSING...

--SO WHAT HAPPENED EXACTLY WHEN WE UPDATED THE VIEW ..LETS TRY TO UNDERSTAND..

UPDATE VIEWANURAG SET [HCL OFFICE]='HCL BBSR' WHERE NAME='MUKUA'

--HERE WHAT IT DID IS LIKE...IN HCL TABLE WHERE HCL BBSR WAS THERE IT CHANGED TO [HCL BBSR]. SO THE UPDATE SUCCEEDS BUT NOT AS EXPECTED ALWAYS...BECAUSE WE WANTED THE OUTPUT LIKE BELOW..

-- [HCL DEP]  [NAME]      [SAPID]     [HCL DEPARTMENT]   [HCL OFFICE]

--    1       ANURAG      514               1             HCL NOIDA

--    2       SIBA        325               2             HCL CHENNAI

--    3       MUKUA       888               3             HCL BBSR

--    4       BISWA       777               4             HCL PUNE

--    3       BIKASH      243               3             HCL BANGALORE

--    3       AKASH       711               3             HCL BANGALORE

--BUT WE GOT OUTPUT LIKE THIS

         <<<<<<<<SO HOPE YOU UNDERSTOOD THE CONCEPT >>>>>>>>>>>>

Kindly give the comment if it helped you..