How to modify the data in a DB2 table

DB2 INSERT Statement :
We can use INSERT statement to insert one or more rows into a DB2 table. The syntax of this statement is different depending on whether we are adding a single row or selected rows.

The syntax of an INSERT statement that ads a single row -

INSERT INTO table-name
    [(column-list)]
     VALUES (value-list) 

Example is given below -

INSERT INTO MM01.CUST1
       ( CUSTNO, FNAME, LNAME, CITY)
VALUES ( :CUSTNO, :FNAME, :LNAME, :CITY) 

To add a single row to a table, we specify  the name of the table we want to add the row to the names of the columns we are supplying data for , and the values for those columns. In above example the names that are preceded by a colon represent fields in the COBOL program called "Host Variables". These variables must be coded in the same sequence as the columns in the column list.

To add more than one row to a table, we include a SELECT statement within the INSERT statement. Then, the SELECT statement retrieves rows from one or more tables based on the conditions we specify, and the INSERT statement will add those rows to another table. See the example below -

INSERT INTO MM01.NEW_CUST
    SELECT *
          FROM MM01.OLD_CUST
          WHERE CUSTNO < '1000' 

In the above example the SELECT statement selects all the columns from the rows in the old customer table with customer number greater than less than 1000 and inserts them into new customer table.

DB2 UPDATE statement :
To change the values of one or more columns in a DB2 table , we use the UPDATE statement. Here we specify the name of the table we want to update, expressions that indicate the columns we want to change and how we want to change them, and a condition that indicates the row or rows we want to change.

The syntax of an INSERT statement that ads a single row -

UPDATE table-name
    SET expression-list
     WHERE selection-condition 

Example is given below -

UPDATE MM01.CUSTOMER
       SET FNAME = :FNAME
           LNAME = :LNAME
           CITY  = :CITY
    WHERE CUSTNO = '27061' 

In above example, the row with customer number equal to '27061' will be updated.

DB2 DELETE statement :
To delete rows from a DB2 table, we use the DELETE statement . In this statement , we specify the table name we want to delete one or more rows from and a condition that indicates the rows we want to delete.

The syntax of an INSERT statement that ads a single row -

DELETE FROM table-1
     WHERE condition 

Example is given below -

DELETE FROM MM01.CUSTOMER
    WHERE CUSTNO = '27061' 

The DELETE statement in above example will delete the row from customer table with customer number equal to 27061.