How To Update View in SQL Developer

In my past article, we took a gander at how to utilize the Make Update View in SQL Developer to make views. In this one, we are moving on and focusing on how to adjust views. We will continue using models on an example data set and information made in the first review so in request to track, head over and read the Creating views in SQL Server part prior to starting with this one.

You can utilize the “OR Supplant” choice. In the event that the view exists it will be supplanted with the new definition or another view will be made. We can utilize Make or Supplant choice to make views instead of dropping the view and recreating it, similarly as with this choice the honors allowed on the view are protected, yet the ward stored projects and view become invalid.

The view will become invalid at whatever point the base table is Rollback SQL Update Query. We can recompile a view using the Modify view proclamation, however prophet automatically recompiles the view whenever it is gotten to. On recompiling the reliant articles become invalid.

Unexpectedly, prior to modifying a view, we will make one more view with somewhat more perplexing T-SQL using totals in it as opposed to having a basic SELECT explanation that is pulling everything from a table. We won’t rehash the grammar since T-SQL is the very same as in Update View in SQL Developer with the exception of the way that instead of the Make saved watchword Adjust is utilized.

Creating view

As I referenced before, we should utilize the code from beneath to make somewhat more mind boggling view:

• Make VIEW vTop3SalesByQuantity
• AS
  • SELECT TOP 3 – – will just return initial 3 records from question
  • Sales.ProductID,
  • Name AS ProductName,
  • SUM(Sales.Quantity) AS TotalQuantity
  • FROM Deals
    • JOIN Items ON Sales.ProductID = Products.ProductID
  • Bunch BY Sales.ProductID,
    • Name
  • Request BY SUM(Sales.Quantity) DESC;

However, before we run the content, we can again feature the SELECT assertion and see what it returns as shown beneath:

Fundamentally, what we are doing here is for every item in the Item table, we are fetching every one of the amounts and add them together per item. As may be obvious, we have our Long-Sleeve Logo Jersey item in various sizes and sold amounts. We just have four items in our table, so that is the reason we’re selecting just the top three records.

Everything looks great, so we can execute the entire Make VIEW SQL articulation to make the view with the SELECT explanation that has Aggregate in it which is a total:

A message in outcome set saying that Make VIEW SQL order finished effectively and showing the recently made view in Item Voyager

The Total is viewed as a total on the grounds that, as a general rule, it adds the numbers together. Accordingly, we likewise have the Gathering BY condition, trailed by Request BY etc., we’d run into certain mistakes.

Modifying view

We should continue on and investigate how we can adjust views. We will take the content of the principal view as an illustration since it has a straightforward SELECT assertion. In the event that you recall the Make VIEW SQL grammar, a view can be altered by just using the Modify VIEW catchphrase instead, and afterward changing the design of the SELECT assertion.

To begin, in SQL Server The executives Studio (SSMS) we can just right-tap the view from Item Traveler, and from the setting menu explore to Prearrange View as | Modify To | New Question Editor Window as shown beneath:

“Script view as” choice from right-click setting menu in Item Traveler in Update View in SQL Developer The executives Studio
SSMS will take the existing design of the view and create the following code in another question editor:

• USE [SQLShackDB]
• GO
• /****** Object: View [dbo].[vEmployeesWithSales] Content Date: 2/25/2020 10:49:32 PM ******/
• SET ANSI_NULLS ON
• GO
• SET QUOTED_IDENTIFIER ON
• GO
• Change VIEW [dbo].[vEmployeesWithSales]
• AS
  • SELECT DISTINCT
    • Employees.*
  • FROM Workers
    • JOIN Deals ON Employees.EmployeeID = Sales.EmployeeID;
• GO

What we are interested in is the SELECT piece of the code. Selecting everything is by and large a terrible thing. Why? For instance, suppose that we have an application using this view and that it’s relying on a particular result Otherwise known as the mark. There could be an issue in the event that we change the underlying table for example add additional sections or eliminate some, and so forth.

Conclusion

In this piece of learning the Make Update View in SQL Developer articulation, we figured out how to utilize the Adjust VIEW order to alter an existing view and change the result. I guaranteed more in the initial segment, yet rather than making this a long and boring article, we’ll continue our excursion in the following one. We haven’t even touched how to utilize the DLM language for inserting information through a view, so that is the very thing we’ll cover in the following piece of the series.