, , ,

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
, , ,

DELETE Statement

The DELETE statement allows you to delete a single record or multiple records from a table.
The syntax for the DELETE statement is:
    DELETE FROM table
    WHERE predicates;
Example #1 : Simple example
Let’s take a look at a simple example:
    DELETE FROM suppliers
    WHERE supplier_name = ‘IBM’;
This would delete all records from the suppliers table where the supplier_name is IBM. You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by running the following SQL statement before performing the delete.
Example #2 : More complex example
You can also perform more complicated deletes.
You may wish to delete records in one table based on values in another table. Since you can’t list more than one table in the FROM clause when you are performing a delete, you can use the EXISTS clause.
For example:
    DELETE FROM suppliers
    WHERE EXISTS
      ( select customers.name
         from customers
         where customers.customer_id = suppliers.supplier_id
         and customers.customer_name = ‘IBM’ );
This would delete all records in the suppliers table where there is a record in the customers table whose name is IBM, and the customer_id is the same as the supplier_id.
/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
, , ,

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
, , ,

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