Parameter and keyword description - update statement, PL-SQL Programming

Assignment Help:

Parameter and Keyword Description: 

table_reference:

This keyword identifies the table or view that should be accessible when you execute the UPDATE statement, and for which you should have the UPDATE privileges.

Subquery:

This is a SELECT statement which gives a set of rows for the processing. 

TABLE (subquery2):

The operand of the TABLE is a SELECT statement which returns a single column value, that should be a nested table or a varray cast as the nested table. The Operator TABLE informs the Oracle that the value is a collection, however not a scalar value.

alias:

This is the other (typically short) name for the referenced table or view and is usually used in the WHERE clause.

column_name:

This is the name of the column which is to be updated. It should be the name of a column in the referenced table or the view. The column name cannot be repetitive in the column_name list. The Column names do not require appearing in the UPDATE statement in similar order that they appear in the table or view.

sql_expression:

This is any of the valid SQL expression.

SET column_name = sql_expression:

This clause assigns the value of the sql_expression to the column recognized by the column_name. If the sql_expression contains the references of the columns in the table being updated, the references are solved in the phrase of the present row. The older column values are used on the right side of the equal sign.

In the illustration below, you raise every employee's salary by 10%. The real value of the sal column is multiplied by 1.10, and then the result is assigned to the sal column overwriting the real value.

UPDATE emp SET sal = sal * 1.10;

SET column_name = (subquery3):

This clause assigns the value retrieve from the database by the subquery3 to the column recognized by the column_name. The sub query should return specifically one row & one column.

SET (column_name, column_name, ...) = (subquery4):

This clause assigns the values retrieve from the database by the subquery4 to the columns in the column_name list. The sub query should return exactly one row which includes all the columns listed.

The columns values return by the sub query are assigned to the columns in the column list in order. The initial value is assigned to the first column in the list; the second value is assigned to the second column in the list, and so on. In the correlated query below, the column item_id is assigned the value stored in the item_num, and the column price is assigned to the value stored in the item_price:

UPDATE inventory inv -- alias

SET (item_id, price) =

(SELECT item_num, item_price FROM item_table

WHERE item_name = inv.item_name);

WHERE search_condition:

This clause prefers that rows to update in the database table. Only the rows that meet the search condition are updated. If you omit the search situation, all the rows in the table are updated

WHERE CURRENT OF cursor_name:

This clause refers to the newest row processed by the FETCH statement related with the cursor identified by the cursor_name. The cursor should be FOR UPDATE and should be open and situated on a row. When the cursor is not open, the CURRENT OF the clause causes an error. If the cursor is open, however no rows have been fetched or the last fetch returned no rows, the PL/SQL raises the predefined exception NO_DATA_FOUND.

returning_clause:

This clause lets you return values from the updated rows, thereby removing the need to SELECT the rows later. You can retrieve the column values into the variables and/or host variables, or into the collections and/or host arrays. Though, you cannot use the RETURNING clause for remote or parallel updates. 


Related Discussions:- Parameter and keyword description - update statement

Cartesian product, Using a Join on 2 tables, select all columns and all row...

Using a Join on 2 tables, select all columns and all rows from the tables without the use of a Cartesian product. Query: SELECT * FROM EMPLOYEE1 JOIN CONTRACT ON EMPLOYEE

Using forall statement - bulk bind performance improvement, Using the FORAL...

Using the FORALL Statement The keyword FORALL instruct the PL/SQL engine to bulk-bind input collections before sending them all to the SQL engine. Though the FORALL statement

Cursor variables as parameters, Cursor Variables As Parameters You can...

Cursor Variables As Parameters You can declare the cursor variables as the formal parameters of the functions and procedures. In the illustration below, you define the REF CUR

PROCEDURE, Create a procedure named DDPROJ_SP that retrieves project inform...

Create a procedure named DDPROJ_SP that retrieves project information for a specific project based on a project ID. The procedure should have two parameters: one to accept a projec

Semijoin and composition - sql, Semijoin and Composition - SQL For sem...

Semijoin and Composition - SQL For semijoin, the dyadic relational operator MATCHING, defined thus: r1 MATCHING r2, where r1 and r2 are relations such that r1 JOIN r2 is de

Write an anonymous block that contains a pl/sql procedure, Write an anonymo...

Write an anonymous block that contains a PL/SQL procedure. The procedure takes two input parameter: oldZip and newZip, and it updates the zipcodes table by replacing all oldZip wit

Triggers, At times, customers make mistakes in submitting their orders and ...

At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a trigger that automatically updates the stock level of all pr

Custom ms access database designed, I would like to have a custom MS Access...

I would like to have a custom MS Access database designed and coded that would help me schedule my customer's orders and that would help me track my employees production output and

Remote operations in pl sql, Remote Operations: As the illustration sh...

Remote Operations: As the illustration shows below, the PL/SQL subprograms can execute the dynamic SQL statements which refer to the objects on a remote database: PROCEDURE

Need azure crm web application with authentication, Need Azure CRM Web Appl...

Need Azure CRM Web Application with two-factor authentication We presently have a CRM-like database stored on MS Azure that we presently access over an MS Access application. It

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd