DBMS/SQL Data Manipulation Language
Page 10-1
DBMS/SQL Data Manipulation Language
Page 10-2
DBMS/SQL Data Manipulation Language
Page 10-3
DBMS/SQL Data Manipulation Language
Addition of Data into Tables:
Requisites for using INSERT command:
If values are specified for all columns in the order specified at creation, then
col_names could be omitted.
Values should match “data type” of the respective columns.
Number of values should match the number of column names mentioned.
All columns declared as NOT NULL should be supplied with a value.
Character strings should be enclosed in quotes.
Date values should be enclosed in quotes.
Values will insert one row at a time.
Query will insert all the rows returned by the query.
The table_name can be a “table” or a “view”. If table_name is a “view”, then the
following restrictions apply:
The “view” cannot have a GROUP BY, CONNECT BY, START WITH,
DISTINCT, UNION, INTERSECT, or MINUS clause or a join.
If the “view” has WITH CHECK OPTION clause, then a row, which will not
be returned by the view, cannot be inserted.
Page 10-4
DBMS/SQL Data Manipulation Language
Inserting Rows into a Table:
Example:
Inserting a row in EMP table giving all values.
INSERT INTO student_master
VALUES(1001,'Amit',10,'11-Jan-80',‘Chennai');
10 is a dept number which exists in DEPARTMENT_MASTER table
Inserting a row in STAFF_MASTER table giving some values.
INSERT INTO staff_master
(staff_code,staff_name,design_code,dept_code)
VALUES(100001,'Arvind',102,30);
This row will be created if all the constraints like NOT NULL are satisfied.
Page 10-5
DBMS/SQL Data Manipulation Language
Page 10-6
DBMS/SQL Data Manipulation Language
Inserting Rows into a Table:
Inserting by using “substitution variables”:
The problem with the INSERT statement is that it adds only “one row” to the table.
However, by using “substitution variables” the speed of data input can be increased.
Whenever a “substitution variable” is placed in a “value” field, the user will be
prompted to enter the “actual value” when the command is executed.
Page 10-7
DBMS/SQL Data Manipulation Language
Deletion of Data from Tables
The table_name can be a “table” or a “view”.
The DELETE command is used to delete one or more rows from a table.
The DELETE statement removes all rows identified by the WHERE clause.
This is another DML, which means we can rollback the deleted data, and
that to make our changes permanent.
If WHERE clause is omitted, all rows from the table are removed. Else all rows
which satisfy the condition are removed.
FROM clause can be omitted without affecting the statement.
Page 10-8
DBMS/SQL Data Manipulation Language
Deletion of Data from Tables
Example 3:
DELETE staff_master WHERE staff_name = ‘Anil’;
Page 10-9
DBMS/SQL Data Manipulation Language
Modifying / Updating existing Data in a Table:
The table_name can be a “table” or a “view”.
The “value” can be a value, an expression, or a query, which returns a single
value.
The UPDATE command provides automatic navigation to the data.
Note: If the WHERE clause is omitted, all rows in the table will be updated by a
value that is currently specified for the field. Else only those rows which satisfy the
condition will be updated.
Page 10-10
DBMS/SQL Data Manipulation Language
Page 10-11
DBMS/SQL Data Manipulation Language
Page 10-12
DBMS/SQL Data Manipulation Language
Page 10-13
DBMS/SQL Data Manipulation Language
MERGE Statement
The MERGE statement is used mostly in data warehouse environments to build the
data in warehouse
Page 10-14
DBMS/SQL Data Manipulation Language
INTO : this is how we specify the target for the MERGE. The target must be either a
table or an updateable view (an in-line view cannot be used here);
USING : the USING clause represents the source dataset for the MERGE. This can
be a single table (as in our example) or an in-line view;
ON () : the ON clause is where we supply the join between the source dataset and
target table. Note that the join conditions must be in parentheses;
WHEN MATCHED: this clause is where we instruct Oracle on what to do when we
already have a matching record in the target table (i.e. there is a join between the
source and target datasets). We obviously want an UPDATE in this case. One of
the restrictions of this clause is that we cannot update any of the columns used in
the ON clause (though of course we don't need to as they already match). Any
attempt to include a join column will raise an unintuitive invalid identifier exception;
and
WHEN NOT MATCHED : this clause is where we INSERT records for which there
is no current match.
Note that sqlplus reports the number of rows merged. This includes both the
updates and inserts. Oracle treats MERGE as a MERGE and not an
UPDATE+INSERT statement. The same is true of SQL%ROWCOUNT in PL/SQL.
Page 10-15
DBMS/SQL Data Manipulation Language
Page 10-16
DBMS/SQL Data Manipulation Language
Page 10-17
DBMS/SQL Data Manipulation Language
Page 10-18
DBMS/SQL Data Manipulation Language
Page 10-19