Merging Rows in Oracle

Merging Rows


Merging Rows

In Oracle The MERGE statement inserts or updates rows in one table by using data from another table. Each row is inserted or updated in the target table depending on an equijoin condition.

This example matches the employee_id in the tab_new table to the employee_id in the employees table. If a match is found, the row is updated to match the row in the employees table.
If the row is not found, it is inserted into the tab_new table.

The complete example for using MERGE in a PL/SQL block is shown on the next notes page.


Now let us create new table tab_new.

SQL> create table tab_new as select employee_id eid,first_name fname,last_name lname from employees;

Table created.

Describe the structure of tab_new table.

SQL> desc tab_new;

Name Null? Type
----------------------------------------- -------- -----------------
EID NUMBER(6)
FNAME VARCHAR2(20)
LNAME NOT NULL VARCHAR2(25)

Delete all records from tab_new table.

SQL> truncate table tab_new;

Table truncated.
--------
After deleting records from tab_new , now you can insert new records using in tabl_new table with the follwing command.

SQL> insert into tab_new values(101,'shweta','Deshpande');

1 row created.

SQL> insert into tab_new values(102,'shalina','khan');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into tab_new values(103,'rafat','shaikh');

1 row created.

SQL> commit;

Commit complete.

Now view all the records from tab_new table.

SQL> select * from tab_new;

EID FNAME LNAME
---------- -------------------- -------------------------
101 shweta Deshpande
102 shalina khan
103 rafat shaikh


Now write an Annonymous block to inserts or updates rows in one table by using merge statement.

SQL> merge into tab_new t
2 using employees e
3 on (t.eid=e.employee_id)
4 when matched then
5 update set
6 t.fname=e.first_name,
7 t.lname=e.last_name
8 when not matched then
9 insert values (e.employee_id,e.first_name,e.last_name)
10 /

107 rows merged.

You can see that If a match is found, the row is updated to match the row in the employees table. If the row is not found, it is inserted into the tab_new table
SQL> select * from tab_new;

EID FNAME LNAME
---------- -------------------- -------------------------
101 Neena Kochhar
102 Lex De Haan
103 Alexander Hunold
198 Donald OConnell
199 Douglas Grant
:
:
:
:
107 rows inserted

MONTHS_BETWEEN,ADD_MONTHS

MONTHS_BETWEEN(date1, date2):
Finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative.
The noninteger part of the result represents a portion of the month.


For Example:-


SELECT employee_id, hire_date,
MONTHS_BETWEEN (SYSDATE, hire_date) Experiance in Months
from employees
/

ADD_MONTHS(date, n): Adds n number of calendar months to date.
The value of n must be an integer and can be negative.
For example We want to display review date of an employee.six-month review date.


select ADD_MONTHS (hire_date, 6) REVIEW from employees;

Spool command in oracle

Spool Of/On

Sends standard output, informational messages, error messages, and/or warning messages generated by the execution to the file.

If file-name does not exist, it is created. If file-name already exist, it is overwritten..

if a directory does not exist, file-name is created in the current directory.

Example

spool on to 'output.txt';

spool on to 'c:\students\output.txt';

Where condition In Sql

SQL Where Condition

1. Get employee details from employees table whose employee name is “King”

Select * from EMPLOYEES where last_NAME='King'

2. Get employee details from employees  table whose employee name are “John” and “Roy”

Select * from EMPLOYEES where FIRST_NAME in ('King','Kochhar')

3. Get employee details from employee table whose employee name are not “John” and “Roy”

Select * from EMPLOYEES  where Last_NAME not in ('King','Kochhar')

Like Clause In Sql

The SQL LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in combination with the LIKE 

operator:The percent sign (%)

The underscore (_)

The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character. The symbols can be used in 

combinations.

Syntax:
The basic syntax of % and _ is as follows:
Example:-


SELECT FROM table_name
WHERE column LIKE 'XXXX%'

or 

SELECT FROM table_name
WHERE column LIKE '%XXXX%'

or

SELECT FROM table_name
WHERE column LIKE 'XXXX_'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX_'
You can combine N number of conditions using AND or OR operators. Here,
 XXXX could be any numeric or string value.