Merge and truncate in sql, PL-SQL Programming

Assignment Help:

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.


Related Discussions:- Merge and truncate in sql

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

Implicit rollbacks, Implicit Rollbacks Before execute the INSERT, UPDA...

Implicit Rollbacks Before execute the INSERT, UPDATE, or DELETE statement, the Oracle marks an implicit savepoint . When the statement fails, the Oracle rolls back to the save

Using trim - collection method, Using TRIM This process has two forms....

Using TRIM This process has two forms. The TRIM removes an element from the end of the collection. The TRIM(n) removes the n elements from the end of the collection. For e.g.

Effects of null for multiple assignments - sql, Effects of NULL for Multipl...

Effects of NULL for Multiple Assignments - SQL If the row expression given as the source for a multiple assignment evaluates to NULL, then NULL is assigned to each target. If

Set operators - sql operators, Set Operators The Set operators combine...

Set Operators The Set operators combine the results of the two queries into one result. The INTERSECT returns all the distinct rows selected by both queries. The MINUS returns

Begin parameter description in pl sql, BEGIN Parameter Description in pl sq...

BEGIN Parameter Description in pl sql: BEGIN: This keyword signals the beginning of the executable section of a PL/SQL block, that contains executable statements. The execut

Example of not exists in sql, Example of NOT EXISTS in SQL Example: Us...

Example of NOT EXISTS in SQL Example: Use of NOT EXISTS CREATE ASSERTION Must_be_enrolled_to_take_exam_alternative1 CHECK ( NOT EXISTS (SELECT StudentId, CourseId

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

Example of add constraint in sql, Example of ADD CONSTRAINT in SQL Exa...

Example of ADD CONSTRAINT in SQL Example: Alternative formulation for MAX_ENROLMENTS ALTER TABLE IS_ENROLLED_ON ADD CONSTRAINT MAX_ENROLMENTS CHECK ((SELECT COUNT (*)

Introduction to SQl and DQL, which operation is used if we are interested i...

which operation is used if we are interested in only certain columns of a table?

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