UPDATE OPERATIONS - IBM Mainframe

The UPDATE term has two distinct meanings in SQL - it is used to represent collectively all the operations like INSERT, UPDATE and DELETE as a class and also specifically to refer the Modify operation. Like SELECT statements the Update statements operate on both tables and views. But it should be noted that, all views are not updateable. We will further discuss regarding updateable and non-updateable views in the chapter dealing with views. Now we will discuss the three Update statements.

INSERT Statement

This statement, as the name suggests, is used for inserting rows into a table. The general syntax of the insert statement is as follows:

INSERT Statement

In the first format a row is inserted into the table having specified values for specified columns. The first literal corresponds to the first column; the second literal corresponds to the second column and so on. In the second format the subquery is evaluated and a copy of the result (usually multiple rows) is inserted into the table. Here also the one-to-one correspondence between the literals and column names holds. In both cases omitting the list of columns is equivalent to specifying all columns.

Single Row INSERT

Suppose you want to add a new supplier, S6, to the supplier table. The values that you want to add are 'Ashok, 25, Bombay. You can do it in two ways:

Single Row INSERT

Now let us take another example, we do not know the STATUS and CITY of a supplier, say S7, but still want to add him to the table. We can do it as follows

Single Row INSERT

A new row is created in the supplier table with the specified supplier number and name and with blank values for status and city columns. Here there are few points that should be noted. While defining the tables if NOT NULL.WJJH DEFAULT was specified to the columns the default values will be set for those unfilled columns. If NOT. NULL was specified then the INSERT will fail and the database will remain unchanged. Otherwise the column is set to null. If the results of a subquery will result in the required values, then instead of the 'VALUE' clause, the subquery can be used.

UPDATE Statement

The UPDATE statement is used to modify or update an already existing row of a table. The syntax for UPDATE statement is:

UPDATE Statement

All rows in the table which satisfies the condition will be updated in accordance with the assignments in the SET clause. If the WHERE clause is omitted all rows will be updated. Given below are some examples:

Change the CITY of supplier SI to New York.

UPDATE Statement

Make the status of all the suppliers to 25.

Make the status of all the suppliers

Make the shipment quantity of all suppliers in London to zero.

DELETE Statement

The DELETE statement is used to delete an already existing row or rows from a table. The syntax for DELETE statement is:

DELETE Statement

All rows in the table, which satisfies the condition, will be deleted. If the WHERE clause is omitted all rows will be deleted. Some examples of the DELETE statement are: 1. Delete the all suppliers based in London.'

DELETE Statement

Delete all rows from the supplier table.

Delete all rows from the supplier table

The update operations (INSERT, UPDATE and DELETE) have a minor drawback, which is worth mentioning. That is, if the WHERE clause in the UPDATE or DELETE includes a subquery, then the FROM clause of that subquery must not refer to the table that is being updated or deleted. Similarly in the subquery that is used in the INSERT statement the FROM clause cannot refer to the table that is the target of the INSERT.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Mainframe Topics