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|
Ibm Mainframe Tutorial
Introduction To Software Development
Introduction To Ibm Mainframes
Tso And Ispf
Jes2, ]es3 And Sms
Introduction To Job Control Language (jcl)
The Job Statement
The Exec Statement
The Job And Exec Statements
The Dd Statement
Procedures And Symbolic Parameters
Generation Data Groups (gdg), Compile/link-edit And Run Jcls
Access Method Services (ams)
Additional Vsam Commands
Introduction To Rexx
Overview Of Rexx
Introduction To Cics
Exception Handling In Cics
Developing A Cics Application
Cics Programming Techniques
Basic Mapping Support (bms)
Transient Data Control
Temporary Storage Control
Interval And Task Control
Cics Application Design
Recovery And Restart
System Security And Intersystem Communication
Cics Debugging Facilities And Techniques
Bms Map Definition Macros And Copylib Members
Cics Response And Abend Codes
Data, Information And Information Processing
Introduction To Database Management Systems
Introduction To Relational Database Management Systems
Database Architecture And Data Modeling
Overview Of Db2
Structured Query Language (sql)
Data Security And Access
Db2 Application Development
Qmf And Db2i
Db2 Performance Monitoring, Utilities And Recovery/restart
Overview Of Information Management System (ims)
Introduction To Vs Cobol Ii
Overview Of Application Development In Vs Cobol Ii
Overview Of The Cobol Program
Sorting And Merging Files
Coding Cobol Programs That Run Under Cics. Ims, Db2 And Ispf
Compiling The Program
Link-editing The Program
Executing The Program
Improving Program Performance
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.