Update Query in SQL Server using a View as source

Lawrence Salvucci
Lawrence Salvucci used Ask the Experts™
I have a view that I want to use to update a field in a table. The view is pulling data from this table and then I am doing some calculations to change the values. Now I want to take that new value from the view and update it back into this table's field. How do I use a view as the source for a update query? This view is also joined with other tables because it needs other data to do these calculations. Just not sure how to write the update query to use the view as the source.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Russell FoxDatabase Developer
Top Expert 2014
You could use a CTE:
; WITH YourTableUpdate
AS (
		, foo = 1 + 3
	FROM YourView
	SET foo = ytu.foo
FROM YourTable yt
	INNER JOIN YourTableUpdate ytu
		ON yt.RecordID = ytu.RecordID

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
You need to use like below..

Update x
SET x.ColName1 = y.ColName1, x.ColName2=y.ColName2....etc
FROM TableName x
ON a.JoiningColumn = y.JoiningColumn
IT Engineer
Distinguished Expert 2017
Lawrence, the View is a logical table so there's no difference to use a View or a Table in an UPDATE statement. Just use the View name instead of a table name and you should be good to go with the Update.
Lawrence SalvucciDirector of Information Technology


Thank you every one. I was able to get the update to work using the view in place of the table.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial