Other SQL Statements to Manipulate Data

MERGE statement

Oracle 9i introduces the MERGE statement that could be thought of as a combination of INSERT and UPDATE. MERGE inserts a row if it does not yet exist and updates specified columns based on given criteria if the target row has previously been inserted. The syntax for the statement is

MERGE INTO [<qualifier>.]<table_name1>
USING [<qualifier>.]<table_name2> ON (<condition>)
WHEN MATCHED THEN 
UPDATE SET {<column> = {<expression> | DEFAULT},...}
WHEN NOT MATCHED THEN 
INSERT [(<column>,...)] VALUES (<expression> | DEFAULT,...);

The statement could be practical in many different situations; for example, imagine ACME, INC. has a central database and a local database for each warehouse. Each location has its own LOCAL_PRODUCT table that structurally is a copy of the PRODUCT table:

CREATE TABLE LOCAL_PRODUCT 
( 
  PROD_ID_N           NUMBER         NOT NULL, 
  PROD_PRICE_N        NUMBER, 
  PROD_NUM_S          VARCHAR2 (10), 
  PROD_DESCRIPTION_S  VARCHAR2 (44)  NOT NULL, 
  PROD_STATUS_S       CHAR (1)       DEFAULT 'Y', 
  PROD_BRAND_S        VARCHAR2 (20)  NOT NULL, 
  PROD_PLTWID_N       NUMBER         NOT NULL, 
  PROD_PLTLEN_N       NUMBER         NOT NULL, 
  PROD_NETWGHT_N      NUMBER, 
  PROD_SHIPWEIGHT_N   NUMBER, 
  CONSTRAINT CHK_LPRODSTATUS 
             CHECK (PROD_STATUS_S in ('N', 'Y')), 
  CONSTRAINT PK_LPRODUCTPRIM PRIMARY KEY ( PROD_ID_N ) );

You can use the MERGE statement to synchronize the contents of LOCAL_PRODUCT with data in PRODUCT. Most likely you don't have to synchronize all columns because some of them are static, i.e., data in these fields changes rarely, if ever. Assuming the values that could change are price, active status,"packaging dimensions, and shipping weight your MERGE statement would be

MERGE INTO local_product lp
USING product p ON (lp.prod_id_n = p.prod_id_n )
WHEN MATCHED THEN UPDATE 
SET      lp.prod_price_n = p.prod_price_n, 
         lp.prod_status_s = p.prod_status_s,
         lp.prod_pltwid_n = p.prod_pltwid_n,
         lp.prod_pltlen_n = p.prod_pltlen_n,
         lp.prod_shipweight_n = p.prod_shipweight_n
WHEN NOT MATCHED THEN INSERT 
VALUES   (p.prod_id_n,
          p.prod_price_n, 
          p.prod_num_s, 
          p.prod_description_s, 
          p.prod_status_s, 
          p.prod_brand_s, 
          p.prod_pltwid_n, 
          p.prod_pltlen_n, 
          p.prod_netwght_n, 
          p.prod_shipweight_n)

Now, when you run this statement for the first time when LOCAL_PRODUCT is empty, all rows from PRODUCT will be inserted into your local table. If scheduled to run on a permanent basis (for example, every hour) the MERGE statement will trace all possible changes in the PRODUCT table and will either update existing rows of LOCAL_PRODUCT appropriately or insert new rows.

TRUNCATE statement

In addition to the standard DML statements described in this chapter, Oracle and MS SQL Server also introduce a TRUNCATE statement that is functionally identical to DELETE without a WHERE clause — it removes all rows from the target table. The difference is that TRUNCATE is much faster and uses fewer system resources than DELETE. The main limitation of TRUNCATE is that you cannot use it on a table referenced by an enabled FOREIGN KEY constraint.

The syntax for TRUNCATE is simple:

TRUNCATE TABLE <table_name>

This example illustrates the use of TRUNCATE statement using Oracle:

-- TRUNCATE fails because PRODUCT is referenced 
-- by foreign key FK_ORDLINE_PRODUCT
SQL> TRUNCATE TABLE PRODUCT;
TRUNCATE TABLE PRODUCT
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
   
-- Disable the constraint and try again
SQL> ALTER TABLE ORDER_LINE DISABLE CONSTRAINT FK_ORDLINE_PRODUCT;
   
Table altered.
   
SQL> TRUNCATE TABLE PRODUCT;
   
Table truncated.
   
-- Re-enable the constraint
SQL> ALTER TABLE ORDER_LINE ENABLE CONSTRAINT FK_ORDLINE_PRODUCT;
   
Table altered.