SQL Table Editor Modify Table Data Using a View

You can modify data of an underlying base table through an SQL View. An SQL View is a virtual table where column and row contents are defined by an SQL query. The SQL Table Editor displays data from the virtual table, but you can change the data of the underlying base table. This provides more flexibility and control over how you present and manipulate data in the SQL Table Editor.

Use the following rules when you want to modify data through a View:

  • Use Views to create a view of the data in one or more tables in the database. You can add calculated columns to your View.

  • You can only update data in a single underlying base table. For example, if you create a View that combines data from four tables, you can only update data from one of those tables.

  • Depending on what you are trying to do, you need the correct permissions to the underlying base table.

Configuration

Set up and configure the SQL Table Editor component:

  1. Inside the appropriate workspace, create and configure an SQL Table Editor component according to your needs.

  2. In the Table Name property, specify the underlying base table that contains the editable data.

  3. In the View Name property, type a name for the view.

  4. Identify columns in the view that do not exist in the underlying table. These columns are excluded from the Insert and Update statements that the SQL Table Editor generates when updating the base table.

  5. Configure a column format for each column in the view that does not exist in the base table.

  6. Set the IsFromTable property to False.

Example

You begin with two tables. The first is a Products table.

The second is an Orders table.

The two tables are linked by the ProdID value, as shown in the previous images.

Next, create a simple view called OrderDetails to show products specific to each order.

Copy 
SELECT dbo.Orders.OrderID, dbo.Orders.prodID, dbo.Orders.Quantity, dbo.Products.productName, 
    dbo.Products.Price
FROM dbo.Orders
INNER JOIN dbo.Products
ON dbo.Orders.prodID = dbo.Products.prodID

This produces the following view:

At this point, you might want to expose the view to users so that they can update order quantities or add additional orders. This data is stored in the underlying base table, Orders.

In the SQL Table Editor, you can set Orders as the Table Name (the base table) and OrderDetails as the View Name.

You now need to identify columns in the view that do not exist in the underlying base table. In this example, that would be productName and Price. For both columns, configure a column format where IsFromTable is set to False. You can also make the columns read-only by setting AllowUpdates to False.

You can now see all columns of the view while also modifying orders.

You can also add calculated columns to views. These columns are virtual and do not exist in the database They derive their values dynamically when the view is executed.

For example, perhaps we would like to add a column that multiplies Quantity times Price. You could update the view with an expression. You can give the expression any name, such as SubTotal in the following example.

Copy 
SELECT dbo.Orders.OrderID, dbo.Orders.prodID, dbo.Orders.Quantity, dbo.Products.productName, 
    dbo.Products.Price,
(dbo.Orders.Quantity*dbo.Products.Price) AS SubTotal
FROM dbo.Orders
INNER JOIN dbo.Products
ON dbo.Orders.prodID = dbo.Products.prodID