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.
This statement, as the name suggests, is used for inserting rows into a table. The general syntax of the insert statement is as follows:
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:
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
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.
The UPDATE statement is used to modify or update an already existing row of a table. The syntax for UPDATE statement is:
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.
Make the status of all the suppliers to 25.
Make the shipment quantity of all suppliers in London to zero.
The DELETE statement is used to delete an already existing row or rows from a table. The syntax for DELETE statement is:
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 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.
IBM Mainframe Related Interview Questions
|IBM Lotus Notes Interview Questions||IBM-CICS Interview Questions|
|COBOL Interview Questions||Linux Interview Questions|
|IBM-JCL Interview Questions||IBM Mainframe Interview Questions|
|IBM AIX Interview Questions||IBM WAS Administration Interview Questions|
|IBM Lotus Domino Interview Questions||IBM Integration Bus Interview Questions|
|Mainframe DB2 Interview Questions||Unix Production Support Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.