Updating table from a view in sql


14-Aug-2017 14:11

However, to create an updatable view, the SELECT statement that defines the view must not contain any of the following elements: If you create a view with the TEMPTABLE algorithm, you cannot update the view.Note that it is sometimes possible to create updatable views based on multiple tables using an inner join. By doing this I can see what my WHERE condition will return to make sure it identifies the same rows I want to update.Once my SELECT statement does return the correct rows I can then copy the WHERE clause from my SELECT statement, and paste it into my UDPATE statement code.Additionally if you forget the WHERE clause altogether, you will update the entire table when you might not intend to.

In my examples above I only updated a single column with my UPDATE statement.

works fine when I try to update all the records in tbl A, however, in this case I only have missing data which I have identified and populated in tbl B.

When I try to update only the missing data the non-matched data is also updated but to NULL. The reason some of your rows are getting NULLs updated is due to the subquery.

In my first TSQL script above, when I create the TOY table, there is typo in the first Toy Name.

I created a Toy Name of “Magic Wnd” when it should have been “Magic Wand”.If you are not exactly sure what rows will be returned by your WHERE clause of an UPDATE statement, it best to take some precautions prior to executing your UPDATE statement.



If you have filter too much, you can always deselect the filter by clicking on it again (turns red).… continue reading »


Read more

You can create links by adding rel="nofollow" tag, which is not searched by search engines. You also should add nofollow tag to the links, you think it doesn't need it.… continue reading »


Read more