, , ,

Merge into in SQL

The merge command syntax is
merge into table1
using table2 on (join_condition)
when matched update set col1 = value
when not matched insert (column_list) values (column_values).
The statement components work in the following way:
1. In the merge into table1 clause, you identify a table into which you would like to update data in an existing row or add new data if the row doesn’t already exist as table1.

2. In the using table2 clause, you identify a second table from which rows will be drawn in order to determine if the data already exists as table2. This can be a different table or the same table as table1. However, if table2 is the same table as table1, or if the two tables have similar columns, then you must use table aliases to preface all column references with the correct copy of the table. Otherwise, Oracle will return an error stating that your column references are ambiguously defined.
In the on (join_condition) clause, you define the join condition to link the two tables together. If table2 in the using clause is the same table as table1 in the merge into clause, or if the two tables have similar columns, then you must use table aliases or the table.column syntax when referencing columns in the join or filter conditions. Otherwise, Oracle will return an error stating that your column references are ambiguously defined.
3. In the when matched then update set col1 = value clause, you define the column(s) Oracle should update in the first table if a match in the second table is found. If table2 in the using clause is the same table as table1 in the merge into clause, or if the two tables have similar columns, then you must use table aliases or the table.column syntax when referencing columns in the update operation. Otherwise, Oracle will return an error stating that your column references are ambiguously defined.
4. In the when not matched then insert (column_list) values (value_list) clause, you define what Oracle should insert into the first table if a match in the second table is not found. If table2 in the using clause is the same table as table1 in the merge into clause, or if the two tables have similar columns, then you must use table aliases or the table.column syntax to preface all column references in column_list. Otherwise, Oracle will return an error stating that your column references are ambiguously defined.
Example
Consider the following scenario. Say you manage a movie theater that is part of a national chain. Everyday, the corporate headquarters sends out a data feed that you put into your digital billboard over the ticket sales office, listing out all the movies being played at that theater, along with showtimes. The showtime information changes daily for existing movies in the feed.
merge into movies M1
using movies M2 on (M2.movie_name = M1.movie_name and M1.movie_name = ‘GONE WITH THE WIND’)
when matched then update set M1.showtime = ‘7:30 PM’
when not matched then insert (M1.movie_name, M1.showtime) values (‘GONE WITH THE WIND’,’7:30 PM’);

/by
, , ,

UPDATE Statement

Data manipulation on Oracle tables does not end after you add new records to your tables. Often, the rows in a table will need to be changed. In order to make those changes, the update statement can be used.
The UPDATE statement allows you to update a single record or multiple records in a table.
The syntax for the UPDATE statement is:
    UPDATE table
    SET column = expression
    WHERE predicates;
Example #1 – Simple example
Let’s take a look at a very simple example.
    UPDATE suppliers
    SET name = ‘HP’
    WHERE name = ‘IBM’;
This statement would update all supplier names in the suppliers table from IBM to HP.
Example #2 – More complex example
You can also perform more complicated updates.
You may wish to update records in one table based on values in another table. Since you can’t list more than one table in the UPDATE statement, you can use the EXISTS clause.
For example:
    UPDATE suppliers    
    SET supplier_name =     ( SELECT customers.name
    FROM customers
    WHERE customers.customer_id = suppliers.supplier_id)
    WHERE EXISTS
      ( SELECT customers.name
        FROM customers
        WHERE customers.customer_id = suppliers.supplier_id);Whenever a supplier_id matched a customer_id value, the supplier_name would be overwritten to the customer name from the customers table.
/by
, , ,

INSERT Statement

The INSERT statement allows you to insert a single record or multiple records into a table.
The general syntax for an insert statement is insert into tablename (column_list) values (valuesl_list), where tablename is the
name of the table you want to insert data into, column_list is the list of columns for which you will define values on the record being added, and values_list is the list of those values you will define. The datatype of the data you add as values in the values list must correspond to the datatype for the column identified in that same position in the column list.
The syntax for the INSERT statement is:

INSERT INTO table_name
(column-1, column-2, … column-n) VALUES (value-1, value-2, … value-n);

Example 1:
INSERT INTO  XX_PO_HEADERS_ALL
(PO_ID, PO_NUMBER, SUPPLIER_NAME) VALUES(6, 10, ‘ARCODA’)
Example 2:
you may not necessarily need to define explicit columns of the table. You only need to do that when you don’t plan to populate every column in the record you are inserting with a value.
insert into employee
values (‘02039′,’WALLA’,’RAJENDRA’,60000,’01-JAN-96′,’604B’);
Example 3:
INSERT INTO suppliers
(supplier_id, supplier_name) SELECT account_no, name FROM customers WHERE city = ‘Newark’;
Example 4:
The following is an example of how you might insert 3 rows into the suppliers table in Oracle.
INSERT ALL
INTO  XX_PO_HEADERS_ALL(PO_ID, PO_NUMBER, SUPPLIER_NAME) VALUES(4, 10, ‘ARCODA’)
INTO  XX_PO_HEADERS_ALL(PO_ID, PO_NUMBER, SUPPLIER_NAME) VALUES(5, 10, ‘ARCODA’)
Select * from dual

/by
, , ,

DML Statements

Data Manipulation Language (DML) is a family of computer languages used by computer programs database users to retrieve, insert, delete and update data in a database.
Currently the most popular data manipulation language is that of SQL, which is used to retrieve and manipulate data in a Relational database. Other forms of DML are those used by IMS/DLI, CODASYL databases (such as IDMS), and others.
Data manipulation languages were initially only used by computer programs, but (with the advent of SQL) have come to be used by people, as well.
Data Manipulation Language (DML) is used to retrieve, insert and modify database information. These commands will be used by all database users during the routine operation of the database. Let’s take a brief look at the basic DML commands:
Data Manipulation Languages have their functional capability organized by the initial word in a statement, which is almost always a verb. In the case of SQL, these verbs are:
    * Select
    * Insert
    * Update
    * Delete
/by
, , ,

Constraints

Constraints are rules you can define in your Oracle tables to restrict the type of data you can place in the tables.


 
Two methods exist for defining constraints: the table constraint method and the column constraint method. The constraint is defined as a table constraint if the constraint clause syntax appears after the column and datatype definitions. The constraint is defined as a column constraint if the constraint definition syntax appears as part of an individual column’s definition. All constraints can be defined either as table constraints or as column constraints, with two exceptions:
  •       Not NULL constraints can only be defined as column constraints.
  •       Primary keys consisting of two or more columns (known also as composite primary keys) can only be defined as table constraints. However, single-column primary keys can be defined either as column or table constraints.
Primary Key
A constraint of this type identifies the column or columns whose singular or combined values identify uniqueness in the rows of your Oracle table. Every row in the table must have a value specified for the primary key column(s).
SQL> create table employee
(
 empid varchar2(5)  constraint pk_employee_01 primary key,
 lastname varchar2(25),
 firstname varchar2(25),
 salary number(10,4)
);      //column constraint method
 
Or cab be simplified as
create table employee
(

 empid varchar2(5)  primary key,
 lastname varchar2(25),
 firstname varchar2(25),
 salary number(10,4)
);      //column constraint method
Each primary key constraint was given a meaningful name when defined. Oracle strongly recommends that you give your constraints meaningful names in this way so that you can easily identify the constraint later
 create table employee
 (
 empid varchar2(5),
 lastname varchar2(25),
 firstname varchar2(25),
 salary number(10,4),
 constraint pk_employee_01  primary key (empid)
);     //table constraint method

Composite Primary Keys
So that you understand the nature of composite primary keys in Oracle for OCP, the following code block shows how to define a composite primary key:
create table names
(
firstname varchar2(10),
lastname varchar2(10),
constraint pk_names_01 primary key (firstname, lastname)
);
Defining Foreign Key Constraints
To help you understand how to define foreign key constraints, let’s think in terms of an example. Let’s say we have our own table called DEPARTMENT, which we just created in the last code block. It lists the department number, name, and location for all departments in our own little company. Let’s also say that we want to create our EMPLOYEE table with another column, called DEPARTMENT_NUM. Because there is an implied parent-child relationship between these two tables with the shared column, let’s make that relationship official by using a foreign key constraint, shown in bold in the following block:
create table employee
(

empid varchar2(5) primary key,
lastname varchar2(25),
firstname varchar2(25),
department_num number(5) references department(department_num) on delete set null,
salary number(10,4)
);  //references table_name(column_name) on delete set null,

For a foreign-key constraint to be valid, the same column appearing in both tables must have exactly the same datatype. You needn’t give the columns the same names, but it’s a good idea to do so. The foreign key constraint prevents the DEPARTMENT_NUM column in the EMP table from ever storing a value that can’t also be found in the DEPT table. The final clause, on delete set null, is an option relating to the deletion of data from the parent table. If someone attempts to remove a row from the parent table that contains a referenced value from the child table, Oracle sets all corresponding values in the child to NULL. The other option is on delete cascade, where Oracle allows remove all corresponding records from the child table when a referenced record from the parent table is removed.
Unique Key Constraints
Let’s say we also want our employee table to store social security or government ID information. The definition of a UNIQUE constraint on the GOVT_ID column prevents anyone from defining a duplicate government ID for any two employees in the table. Take a look at the following example, where the unique constraint definition is shown in bold:
create table employee
(
empid varchar2(5) primary key,
lastname varchar2(25),
firstname varchar2(25),
govt_id number(10) unique,
salary number(10,4),
department_num number(5) references department (department_num),
);
Defining Other Types of Constraints
The last two types of constraints are not NULL and CHECK constraints. By default, Oracle allows columns to contain NULL values. The not NULL constraint prevents the data value defined by any row for the column from being NULL. By default, primary keys are defined to be not NULL. All other columns can contain NULL data, unless you explicitly define the column to be not NULL. CHECK constraints allow Oracle to verify the validity of data being entered on a table against static criteria. For example, you could specify that the SALARY column cannot contain values over $250,000. If someone tries to create an employee row with a salary of $1,000,000 per year, Oracle would return an error message saying that the record data defined for the SALARY column has violated the CHECK constraint for that column. Let’s look at a code example where both not NULL and check constraints are defined in bold:
create table employee
(
empid varchar2(5) primary key,

department_num number(5) references department (department_num),
lastname varchar2(25) not null,
firstname varchar2(25) unique,
salary number(10,4) check (salary <=250000),
govt_id number(10) unique
);

Adding Integrity Constraints to Existing Tables
Another constraint-related activity that you may need to do involves adding new constraints to an existing table. This can be easy if there is no data in the table already, but it can be a nightmare if data already exists in the table that doesn’t conform to the constraint criteria. The simplest scenario for adding the constraint is to add it to the database before data is inserted. Take a look at the following code block:

SQL> create table employee
  2  (empid varchar2(5),
  3   lastname varchar2(25),
  4   firstname varchar2(25),
  5   salary number(10,4),
  6   department_num number(5),
  7   govt_id number(10));
Table created.
alter table employee
add constraint pk_employee_01 primary key (empid);
alter table employee
add constraint fk_employee_01 foreign key (department_num) references department (department_num);
alter table employee
add constraint ck_employee_01 check (salary <=250000);
alter table employee
add constraint uk_employee_01 unique (govt_id);
alter table employee modify
(lastname not null);

Disabling Constraints
A constraint can be turned on and off. When the constraint is disabled, it will no longer do its job of enforcing rules on the data entered into the table. The following code block demonstrates some sample statements for disabling constraints:
alter table employee
disable primary key;

alter table employee

disable constraint uk_employee_01;

You may experience a problem if you attempt to disable a primary key when existing foreign keys depend on that primary key. This problem is shown in the following situation:
Enabling a Disabled Constraint
When the constraint is later enabled, the rules defined for the constraint are once again enforced, rendering the constraint as effective as it was when it was first added to the table. You can enable a disabled constraint as follows:

alter table department
enable primary key;
alter table employee
enable uk_employee_01;

Removing Constraints
Usually, there is little about a constraint that will interfere with your ability to remove it, so long as you either own the table or have been granted appropriate privileges to do so. When a constraint is dropped, any index associated with that constraint (if there is one) is also dropped. Here is an example:
alter table employee

drop unique (govt_id);

alter table employee

drop primary key cascade;

alter table employee
drop constraint ck_employee_01;

An anomaly can be found when disabling or dropping not NULL constraints. You cannot disable a not NULL constraint, per se—a column either accepts NULL values or it doesn’t. Therefore, you must use the alter table modify clause in all situations where the not NULL constraints on a table must be added or removed. Here’s an example:
alter table employee
modify (lastname null);
alter table employee
modify (lastname not null);

/by