DB2 SQL - DML (Data Manipulation Language)

DB2 SQL - DML (Data Manipulation Language)

You can use following statements to manipulate data 

on db2 database. 

SELECT
INSERT
UPDATE
DELETE


SELECT

SELECT statement is used to get all data or required data from the table.

Simple Syntax 

SELECT < Column Names > / * / function(< Column Name >) FROM < TABLE NAME > 
[ WHERE < Condition > .... ] 

Example (s) 

1. To get all data from EMPLOYEE table , issue

SELECT * FROM EMPLOYEE

Following is a the result set returned by the query

 

INSERT 

INSERT statement is used to insert record(s) into table / view. Inserting a row into view also inserts the row into table.

Simple Syntax

INSERT INTO < TABLE NAME / VIEW NAME > [ (,...) ] VALUES ( value1, value2,.... )

Example 1

INSERT INTO EMPLOYEE ( EMP_ID, EMP_FIRST_NAME,EMP_M_NAME,EMP_LAST_NAME, SALARY, DEP) 
VALUES ( 10035, 'JOHN', 'X', 'ABRAHAM', 20000.00, 003 ) ,
( 10036, 'SRINIVAS', 'X', 'GARIPELLA', 25000.00, 003 )

Above insert statemet, insert two records into EMPLOYEE Table, Now table contains following records. 

 
 
Example 2


Specify a fullselect to identify data that is to be copied from other tables or views. A fullselect is a statement that generates a result table. For example:

CREATE TABLE emp LIKE EMPLOYEE
INSERT INTO emp
SELECT EMP_ID,EMP_FIRST_NAME,EMP_M_NAME,EMP_LAST_NAME,SALARY, DEP 
FROM EMPLOYEE
WHERE DEP = 002


UPDATE

UPDATE statement is used to update the data on a table or a view. You can change the value of one or more columns for each row that satisfied the condition.

UPDATE EMPLOYEE 
SET SALARY = 20000,
DEP = 003
WHERE EMP_ID = 11023

This update statement update the one record, since only one record can satisfy the where condition. After executing this update statement, table contain following data.

DELETE

DELETE statement is used to delete the record(s) from a table or a view. You can delete all records in the table or selected record which satisfies the condition.

Example 1 - To delete all records from the table, We need to use following command

DELETE * FROM EMPLOYEE

Example 2 - To delete some records which satisfies the given condition. 

DELETE FROM EMPLOYEE 
WHERE EMP_ID = 10036

After executing above query one record will be deleted from the table.