how to update sql view - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to update sql view

Author

Topic

yns.emre
Starting Member

11 Posts

Posted - 2009-01-08 : 06:26:04

i have a view,can i update he view like normal sql update or not?

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-08 : 06:48:14

Yes, we can update the view also

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-08 : 07:55:29

but the view must be built on only one table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 08:04:16

quote:
Originally posted by darkdusky

but the view must be built on only one table

So not true at all.
If you have a primary key in all related tables in the view, you can update through the view.

See this example

CREATE TABLE	Table1
		(
			col1 INT PRIMARY KEY
		)

CREATE TABLE	Table2
		(
			pk INT IDENTITY(1, 1) PRIMARY KEY,
			col2 INT FOREIGN KEY REFERENCES Table1(col1),
			col3 INT
		)

INSERT	Table1
SELECT	1 UNION ALL
SELECT	2

INSERT	Table2
SELECT	1, 1 UNION ALL
SELECT	2, 2
GO
CREATE VIEW vwTable
AS

SELECT		t1.col1,
		t2.pk,
		t2.col2,
		t2.col3
FROM		Table1 AS t1
INNER JOIN	Table2 AS t2 ON t2.col2 = t1.col1
GO

SELECT	*
FROM	vwTable

UPDATE	vwTable
SET col3 = 5
WHERE col1 = 2

SELECT	*
FROM	vwTable

DROP VIEW	vwTable

DROP TABLE	Table2,
		Table1

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 08:24:49

even otherwise you can make a view updatable using an INSTEAD OF TRIGGER which actually does update on base tables.

revelator
Starting Member

32 Posts

Posted - 2009-01-08 : 09:12:34

But only one base table can be modified, not both. To continue your example, try to execute this statement instead of the UPDATE posted previously.

UPDATE vwTable
SET col3 = 5, col1 = 5
WHERE col1 = 2

quote:

Originally posted by Peso
So not true at all.
If you have a primary key in all related tables in the view, you can update through the view.

See this example

CREATE TABLE	Table1
		(
			col1 INT PRIMARY KEY
		)

CREATE TABLE	Table2
		(
			pk INT IDENTITY(1, 1) PRIMARY KEY,
			col2 INT FOREIGN KEY REFERENCES Table1(col1),
			col3 INT
		)

INSERT	Table1
SELECT	1 UNION ALL
SELECT	2

INSERT	Table2
SELECT	1, 1 UNION ALL
SELECT	2, 2
GO
CREATE VIEW vwTable
AS

SELECT		t1.col1,
		t2.pk,
		t2.col2,
		t2.col3
FROM		Table1 AS t1
INNER JOIN	Table2 AS t2 ON t2.col2 = t1.col1
GO

SELECT	*
FROM	vwTable

UPDATE	vwTable
SET col3 = 5
WHERE col1 = 2

SELECT	*
FROM	vwTable

DROP VIEW	vwTable

DROP TABLE	Table2,
		Table1

E 12°55'05.63"
N 56°04'39.26"

yns.emre
Starting Member

11 Posts

Posted - 2009-01-08 : 09:16:06

thanks everyone i will try

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 09:19:01

That is almost always the case, that affecting only one base table is allowed when updating a view.

There are cases that affects two (or more) tables when updating, like partitioning.
See http://weblogs.sqlteam.com/peterl/archive/2007/09/24/Horizontal-partitioning.aspx

E 12°55'05.63"
N 56°04'39.26"

revelator
Starting Member

32 Posts

Posted - 2009-01-08 : 09:26:53

Nice article Peso, thanks! :)

quote:
Originally posted by Peso

That is almost always the case, that affecting only one base table is allowed when updating a view.

There are cases that affects two (or more) tables when updating, like partitioning.
See http://weblogs.sqlteam.com/peterl/archive/2007/09/24/Horizontal-partitioning.aspx

E 12°55'05.63"
N 56°04'39.26"

Waiting for the Great Leap Forwards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources