Aliases and Merge

Agenda :

Oracle Introduction

USING ALIASES

CREATE WITH SELECT

We can create a table using existing table [along with data].

Syntax:
Create table <new_table_name> [col1, col2, col3 ... coln] as select * from <old_table_name>;

Ex:
SQL> create table student1 as select * from student;

Creating table with your own column names.
SQL> create table student2(sno, sname, smarks) as select * from student;

Creating table with specified columns.
SQL> create table student3 as select no,name from student;

Creating table with out table data.
SQL> create table student2(sno, sname, smarks) as select * from student where 1 = 2;

In the above where clause give any condition which does not satisfy.

INSERT WITH SELECT

Using this we can insert existing table data to a another table in a single trip. But the table structure should be same.

Syntax:
Insert into <table1> select * from <table2>;

Ex:
SQL> insert into student1 select * from student;

Inserting data into specified columns
SQL> insert into student1(no, name) select no, name from student;

COLUMN ALIASES

Syntax:
Select <orginal_col> <alias_name> from <table_name>;

Ex:
SQL> select no sno from student; or
SQL> select no "sno" from student;

TABLE ALIASES

If you are using table aliases you can use dot method to the columns.

Syntax:
Select <alias_name>.<col1>, <alias_name>.<col2> ... <alias_name>.<coln> from <table_name> <alias_name>;

Ex:
SQL> select s.no, s.name from student s;

USING MERGE

MERGE

You can use merge command to perform insert and update in a single command.

Ex:

SQL> Merge into student1 s1
        Using (select *From student2) s2
        On(s1.no=s2.no)
        When matched then
        Update set marks = s2.marks
        When not matched then
        Insert (s1.no,s1.name,s1.marks)
        Values(s2.no,s2.name,s2.marks);

In the above the two tables are with the same structure but we can merge different structured tables also but the datatype of the columns should match.

Assume that student1 has columns like no,name,marks and student2 has columns like no, name, hno, city.

SQL> Merge into student1 s1
        Using (select *From student2) s2
        On(s1.no=s2.no)
        When matched then
        Update set marks = s2.hno
        When not matched then
        Insert (s1.no,s1.name,s1.marks)
        Values(s2.no,s2.name,s2.hno);

MULTIBLE INSERTS

We have table called DEPT with the following columns and data

DEPTNO	DNAME	LOC
--------	--------	----
10		accounting	new york
20		research 	dallas
30		sales 		Chicago
40		operations	boston

CREATE STUDENT TABLE
SQL> Create table student(no number(2),name varchar(2),marks number(3));

MULTI INSERT WITH ALL FIELDS

     SQL> Insert all
             Into student values(1,'a',100)
             Into student values(2,'b',200)
             Into student values(3,'c',300)
             Select *from dept where deptno=10;

-- This inserts 3 rows

MULTI INSERT WITH SPECIFIED FIELDS

     SQL> insert all
             Into student (no,name) values(4,'d')
             Into student(name,marks) values('e',400)
             Into student values(3,'c',300)
             Select *from dept where deptno=10;

-- This inserts 3 rows

MULTI INSERT WITH DUPLICATE ROWS

     SQL> insert all
             Into student values(1,'a',100)
             Into student values(2,'b',200)
             Into student values(3,'c',300)
             Select *from dept where deptno > 10;

-- This inserts 9 rows because in the select statement retrieves 3 records (3 inserts for each row retrieved)

MULTI INSERT WITH CONDITIONS BASED

     SQL> Insert all
             When deptno > 10 then
             Into student1 values(1,'a',100)
             When dname = 'SALES' then
             Into student2 values(2,'b',200)
             When loc = 'NEW YORK' then
             Into student3 values(3,'c',300)
             Select *from dept where deptno>10;

-- This inserts 4 rows because the first condition satisfied 3 times, second condition satisfied once and the last none.

MULTI INSERT WITH CONDITIONS BASED AND ELSE

    SQL> Insert all
            When deptno > 100 then
            Into student1 values(1,'a',100)
            When dname = 'S' then
            Into student2 values(2,'b',200)
            When loc = 'NEW YORK' then
            Into student3 values(3,'c',300)
            Else 
            Into student values(4,'d',400)
            Select *from dept where deptno>10;

-- This inserts 3 records because the else satisfied 3 times

MULTI INSERT WITH CONDITIONS BASED AND FIRST

     SQL> Insert first
             When deptno = 20 then
             Into student1 values(1,'a',100)
             When dname = 'RESEARCH' then
             Into student2 values(2,'b',200)
             When loc = 'NEW YORK' then
             Into student3 values(3,'c',300)
             Select *from dept where deptno=20;

-- This inserts 1 record because the first clause avoid to check the remaining conditions once the condition is satisfied.

MULTI INSERT WITH CONDITIONS BASED, FIRST AND ELSE

     SQL> Insert first
             When deptno = 30 then
              Into student1 values(1,'a',100)
              When dname = 'R' then
              Into student2 values(2,'b',200)
              When loc = 'NEW YORK' then
              Into student3 values(3,'c',300)
              Else
              Into student values(4,'d',400)
              Select *from dept where deptno=20;

-- This inserts 1 record because the else clause satisfied once

MULTI INSERT WITH MULTIBLE TABLES

    SQL> Insert all
            Into student1 values(1,'a',100)
            Into student2 values(2,'b',200)
            Into student3 values(3,'c',300)
            Select *from dept where deptno=10;

-- This inserts 3 rows

** You can use multi tables with specified fields, with duplicate rows, with conditions, with first and else clauses.

BACK