Good SCJP Handbook for Reference

http://www.witscale.com/ebook/Browsable/ebook000.htm


______________________________________________________________________

Merge/ Upsert Sql introduced with Oracle 9i

In version 9i, Oracle added a new DML command, MERGE. This command was improved in 10g. MERGE is generally associated with Data Warehouse and ETL but truthfully, it can be used anywhere you need to move data from one table to another. The MERGE command, also called an UPSERT, is a combination of INSERT and UPDATE.

The basic syntax of a MERGE is:


MERGE INTO destination_table dest
USING (SELECT col1, col2, col3 FROM source_table) source
ON (dest.col1 = source.col1)
WHEN MATCHED THEN
UPDATE SET dest.col2 = source.col2,
dest.col3 = source.col3
WHEN NOT MATCHED THEN
INSERT (dest.col1, dest.col2, dest.col3)
VALUES (source.col1, source.col2, source.col3)
/

That is a basic MERGE. The WHEN MATCHED clause as well as the WHEN NOT MATCHED clause is optional. You can leave off the WHEN MATCHED if you need to insert records and there might be dupes.

MERGE INTO designates the table where the data is going. USING selects data from a table (or tables, or even a view). ON is the matching criteria. WHEN MATCHED means that a match was found (UPDATE) and WHEN NOT MATCHED a match was not found (INSERT).


Good Example 1

The following example will MERGE values from the table DEPT_ONLINE into a table called DEPT:
SQL> SELECT * FROM dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
60 HELP DESK PITTSBURGH
40 OPERATIONS BOSTON

SQL> SELECT * FROM dept_online;

DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
20 RESEARCH DEV DALLAS
50 ENGINEERING WEXFORD


SQL> MERGE INTO dept d
USING (SELECT deptno, dname, loc
FROM dept_online) o
ON (d.deptno = o.deptno)
WHEN MATCHED THEN
UPDATE SET d.dname = o.dname, d.loc = o.loc
WHEN NOT MATCHED THEN
INSERT (d.deptno, d.dname, d.loc)
VALUES (o.deptno, o.dname, o.loc);

3 rows merged.


SQL> SELECT * FROM dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DEV DALLAS
30 SALES CHICAGO
50 ENGINEERING WEXFORD
60 HELP DESK PITTSBURGH
40 OPERATIONS BOSTON

6 rows selected.

Recalling Mail in Microsoft Outlook

If you send a mail from Outlook and if it has some mistake in it
and you wantedto recall it
Step 1
Go to the Sent Items folder in Outlook and open the message you want to recall.

Step 2
Click on Recall This Message in the Actions menu.
(Check Tools menu if you didnt find in Actions)

Step 3
If your message hasn't already been opened by the recipient,
either delete the message or replace it with a new message.

(If the recipient has already seen,we are given up :-)
But there's a chance that some of the recipients might not have already checked it)

Disclaimer: Chaithu.com does not host any files on its own servers.It points only to various links on the Internet that already exist

Site Author, Designer, Publisher and Maintenance by Raja Chaithanya BANGARU...Click Here to E-mail me

Copyright © 2008 Chaithu.com with Chaithanya BANGARU. Some rights reserved