Updating Db2 data by using UPDATE statements

You can change the data in a table by using the UPDATE statement or the MERGE statement.

The UPDATE statement modifies zero or more rows of a table, depending on how many rows satisfy the search condition that you specify in the WHERE clause.

You can use an UPDATE or MERGE statement to specify the values that are to be updated in a single row. You can specify constants, host variables, expressions, DEFAULT, or NULL. Specify NULL to remove a value from a row's column (without removing the row).

Example UPDATE statements

Begin general-use programming interface information.

  • Suppose that an employee gets a promotion. To update several items of the employee's data in the NEWEMP table that reflects the move, use this UPDATE statement: 

    UPDATE NEWEMP
  SET JOB = 'MGR',
  DEPT = 'E21'
  WHERE EMPNO = '100125';

  • Change telephone number for employee number 000190 to 3565 in DSN8C10.EMP.

       UPDATE DSN8C10.EMP
     SET PHONENO='3565'
     WHERE EMPNO='000190';

  • Give each member of department D11 a 100-dollar raise.

      UPDATE DSN8C10.EMP
    SET SALARY = SALARY + 100
    WHERE WORKDEPT = 'D11';

  • Employee 000250 is going on a leave of absence. Set the employee's pay values (SALARY, BONUS, and COMMISSION) to null.

       UPDATE DSN8C10.EMP
     SET SALARY = NULL, BONUS = NULL, COMM = NULL
     WHERE EMPNO='000250';
    Alternatively, the statement could also be written as follows: 
       UPDATE DSN8C10.EMP
     SET (SALARY, BONUS, COMM) = (NULL, NULL, NULL)
     WHERE EMPNO='000250';

  • Assume that a column named PROJSIZE has been added to DSN8C10.EMP. The column records the number of projects for which the employee's department has responsibility. For each employee in department E21, update PROJSIZE with the number of projects for which the department is responsible.

       UPDATE DSN8C10.EMP
     SET PROJSIZE = (SELECT COUNT(*)
                     FROM DSN8C10.PROJ
                     WHERE DEPTNO = 'E21') 
     WHERE WORKDEPT = 'E21';
  • Double the salary of the employee represented by the row on which the cursor C1 is positioned. 
       EXEC SQL UPDATE DSN8C10.EMP
     SET SALARY = 2 * SALARY
     WHERE CURRENT OF C1;

  • Assume that employee table EMP1 was created with the following statement:

       CREATE TABLE EMP1
     (EMP_ROWID    ROWID GENERATED ALWAYS,
      EMPNO        CHAR(6),
      NAME         CHAR(30),
      SALARY       DECIMAL(9,2),
      PICTURE      BLOB(250K),
      RESUME       CLOB(32K));
    Assume that host variable HV_EMP_ROWID contains the value of the ROWID column for employee with employee number '350000'. Using that ROWID value to identify the employee and user-defined function UPDATE_RESUME, increase the employee's salary by $1000 and update that employee's resume. 
       EXEC SQL UPDATE EMP1
     SET SALARY = SALARY + 1000,
         RESUME = UPDATE_RESUME(:HV_RESUME)
     WHERE EMP_ROWID = :HV_EMP_ROWID;

  • In employee table X, give each employee whose salary is below average a salary increase of 10%.

      EXEC SQL UPDATE EMP X
    SET SALARY = 1.10 * SALARY
    WHERE SALARY < (SELECT AVG(SALARY) FROM EMP Y
    WHERE X.JOBCODE = Y.JOBCODE);

  • Raise the salary of the employees in department 'E11' whose salary is below average to the average salary.

      EXEC SQL UPDATE EMP T1
    SET SALARY = (SELECT AVG(T2.SALARY) FROM EMP T2)
    WHERE WORKDEPT = 'E11' AND
          SALARY < (SELECT AVG(T3.SALARY) FROM EMP T3);
  • Give the employees in department 'E11' a bonus equal to 10% of their salary. 
      EXEC SQL 
    DECLARE C1 CURSOR FOR  
      SELECT BONUS  
      FROM DSN8710.EMP 
      WHERE WORKDEPT = 'E12'  
      FOR UPDATE OF BONUS;  
  EXEC SQL  
    UPDATE DSN8710.EMP  
      SET BONUS = ( SELECT .10 * SALARY FROM DSN8710.EMP Y  
                    WHERE EMPNO = Y.EMPNO ) 
      WHERE CURRENT OF C1;
  • Assuming that cursor CS1 is positioned on a rowset consisting of 10 rows in table T1, update all 10 rows in the rowset. 
    EXEC SQL UPDATE T1 SET C1 = 5 WHERE CURRENT OF CS1;
  • Assuming that cursor CS1 is positioned on a rowset consisting of 10 rows in table T1, update the fourth row of the rowset. 
    short ind1, ind2;

int n, updt_value;

stmt = 'UPDATE T1 SET C1 = ? WHERE CURRENT OF CS1 FOR ROW ? OF ROWSET'

ind1 = 0;

ind2 = 0;

n = 4;

updt_value = 5;

...

strcpy(my_sqlda.sqldaid,"SQLDA");

my_sqlda.sqln = 2;

my_sqlda.sqld = 2;

my_sqlda.sqlvar[0].sqltype = 497;
my_sqlda.sqlvar[0].sqllen = 4;
my_sqlda.sqlvar[0].sqldata = (int *) &updt_value;
my_sqlda.sqlvar[0].sqlind = (short *) &ind1;

my_sqlda.sqlvar[1].sqltype = 497;
my_sqlda.sqlvar[1].sqllen = 4;
my_sqlda.sqlvar[1].sqldata = (int *) &n;
my_sqlda.sqlvar[1].sqlind = (short *) &ind2;

EXEC SQL PREPARE S1 FROM :stmt;

EXEC SQL EXECUTE S1 USING DESCRIPTOR :my_sqlda;

  • Assume that table POLICY exists and that it is defined with a single inclusive-exclusive period, BUSINESS_TIME. The table contains a row where column BK has a value of 'P138', column CLIENT has a value of 'C882', column TYPE has a value of 'PPO', and the period has value ('2013-01-01', '2020-12-31'). Update the portion of the row beginning from '2014-01-01' to set the TYPE column to 'HMO':

    UPDATE POLICY
	FOR PORTION OF BUSINESS_TIME 
		FROM '2014-01-01' TO '9999-12-31'
		SET TYPE='HMO'
		WHERE BK='P138', CLIENT='C882';

    After the UPDATE statement is processed, the table contains 2 rows in place of the original row. One row with period value ('2013-01-01', '2014-01-01') represents a value of 'PPO' for the TYPE column (the value before the update) and the other row with period value ('2014-01-01', '2020-12-31') represents a value of 'HMO' for the TYPE column (that began with the UPDATE statement).

  • Suppose that the INTARRAY and CHARARRAY array types, the INTA, CHARA, and SI variables, and the T1 table are defined as follows:Start of change
    CREATE TYPE INTARRAY AS INTEGER ARRAY [6];
CREATE TYPE CHARARRAY AS CHAR(20) ARRAY [7];
CREATE VARIABLE INTA AS INTARRAY;
CREATE VARIABLE CHARA AS CHARARRAY;
CREATE VARIABLE SI INT;
CREATE TABLE T1 (COL1 CHAR(7), COL2 INT);
    End of change

    Assign values to CHARA, INTA, and SI.

    SET CHARA = ARRAY [ 'a', 'b', 'c' ];
SET INTA = ARRAY [ 1, 2, 3, 4, 5 ];
SET SI = 1;

    Insert a row into table T1, and then update the row values using values from the CHARA and INTA arrays, which are indexed by the value of variable SI.

    INSERT INTO T1 VALUES ('abc', 10);
UPDATE T1 
 SET COL1 = CHARA[SI],
 COL2 = INTA[SI];

    In the table row, COL1 now contains 'a', and COL2 contains 1.

    Set the value of column COL2 for all rows to the cardinality of array INTA.

    UPDATE T1
 SET COL2 = CARDINALITY(INTA);

    In the table row, COL2 now contains 5.

  • Assume that table POLICY exists and that it is defined with a single inclusive-inclusive period, BUSINESS_TIME. The table contains a row where column BK has a value of 'P138', column CLIENT has a value of 'C882', column TYPE has a value of 'PPO', and period has value ('2013-01-01', '2020-12-31'). Suppose that you issue the following UPDATE statement:

    UPDATE POLICY
FOR PORTION OF BUSINESS_TIME 
BETWEEN '2014-01-01' AND '9999-12-31'
SET TYPE='HMO'
WHERE BK='P138', CLIENT='C882';

    After the UPDATE statement is processed, the table contains 2 rows in place of the original row. One row with period value ('2013-01-01', '2013-12-31') has a value of 'PPO' for the TYPE column (the value before the update) and the other row with period value ('2014-01-01', '2020-12-31') has a value of 'HMO' for the TYPE column.

End general-use programming interface information.