Merge and truncate in sql, PL-SQL Programming

MERGE and TRUNCATE in SQL

SQL has two more table update operators, MERGE and TRUNCATE. MERGE, like INSERT, takes a source table s and uses it to update a target table t. Briefly, a MERGE statement specifies a matching condition to determine which rows of s have at least one matching row in t (under that specified matching condition). It then specifies an open-ended series of conditions to be applied to each row of s paired with actions to be applied on t.

WHEN MATCHED AND c1 THEN x1 specifies that action x1, necessarily an UPDATE or DELETE, is to be applied on t for each matching row in s that satisfies the condition c1. WHEN NOT MATCHED AND c2 THEN x2 specifies that action x2, necessarily an INSERT, is to be applied on t for each non-matching row in s that satisfies the condition c2. The curiously named TRUNCATE statement deletes all the rows from its specified target, bypassing any triggered actions, including compensatory actions, specified for that target. The target must be a base table.

Posted Date: 1/18/2013 11:40:57 PM | Location : United States







Related Discussions:- Merge and truncate in sql, Assignment Help, Ask Question on Merge and truncate in sql, Get Answer, Expert's Help, Merge and truncate in sql Discussions

Write discussion on Merge and truncate in sql
Your posts are moderated
Related Questions
Exceptions An exception is the runtime error or warning condition that can be predefined or user-defined. The Predefined exceptions are raised implicitly through runtime system

DBMS_OUTPUT: The Package DBMS_OUTPUT enables you to display output from the PL/SQL subprograms and blocks, that makes it easier to test and debug them. The procedure put_ line

Using COMMIT The COMMIT statements end the present transaction and make permanent any changes made during that transaction. Till you commit the changes, other users cannot acc

Anatomy of a Command Figure, showing a simple SQL command, is almost identical to its counterpart in the theory book. The only difference arises from the fact that SQL uses a

Example of DELETE - SQL As with UPDATE, a FOR PORTION OF clause can be specified if the target table has a defined period name, as illustrated in Example. Example: Deleting

Exception handling In the PL/SQL, a warning or error condition is known as an exception. The Exceptions can be internally defined (by the run-time system) or user defined. The

Closing a Cursor Variable The CLOSE statement disables the cursor variable. After that, the related result set is undefined. The syntax for the same is as shown below: CLOS

Expressions   An expression is a randomly complex combination of the constants, variables, literals, operators, & function calls. The simplest expression is the single variabl

EXCEPTION_INIT Pragma The pragma EXCEPTION_INIT relates an exception name with an Oracle error number. Which allow you to refer to any internal exception by the name and to wri

Stored Subprograms Normally, tools (like Oracle Forms) which incorporate the PL/SQL engine can store subprograms locally for later, strictly local execution. Though, to become