UPDATE

To update two fields you may use an example like this:

UPDATE table1 t1 
 SET (col1, col2) = (
  SELECT col3, col4 
  FROM  table2 t2 
  WHERE t1.col8=t2.col9
 )

• Example 1:  Change the job (JOB) of employee number (EMPNO) ‘000290’ in the EMPLOYEE table to ‘LABORER’.

     UPDATE EMPLOYEE
        SET JOB = 'LABORER'
        WHERE EMPNO = '000290'

• Example 2:  Increase the project staffing (PRSTAFF) by 1.5 for all projects that department (DEPTNO) ‘D21’ is responsible for in the PROJECT table.

     UPDATE PROJECT
        SET PRSTAFF = PRSTAFF + 1.5
        WHERE DEPTNO = 'D21'

• Example 3:  All the employees except the manager of department (WORKDEPT) ‘E21’ have been temporarily reassigned. Indicate this by changing their job (JOB) to the null value and their pay (SALARY, BONUS, COMM) values to zero in the EMPLOYEE table.

     UPDATE EMPLOYEE
       SET JOB=NULL, SALARY=0, BONUS=0, COMM=0
       WHERE WORKDEPT = 'E21' AND JOB <> 'MANAGER'

  This statement could also be written as follows.

     UPDATE EMPLOYEE
       SET (JOB, SALARY, BONUS, COMM) = (NULL, 0, 0, 0)
       WHERE WORKDEPT = 'E21' AND JOB <> 'MANAGER'

• Example 4: Update the salary and the commission column of the employee with employee number 000120 to the average of the salary and of the commission of the employees of the updated row’s department, respectively.

     UPDATE (SELECT EMPNO, SALARY, COMM,
       AVG(SALARY) OVER (PARTITION BY WORKDEPT),
       AVG(COMM) OVER (PARTITION BY WORKDEPT)
       FROM EMPLOYEE E) AS E(EMPNO, SALARY, COMM, AVGSAL, AVGCOMM)
     SET (SALARY, COMM) = (AVGSAL, AVGCOMM)
     WHERE EMPNO = '000120'

  The previous statement is semantically equivalent to the following statement, but requires only one access to the EMPLOYEE table, whereas the following statement specifies the EMPLOYEE table twice.

     UPDATE EMPLOYEE EU
       SET (EU.SALARY, EU.COMM)
       =
     (SELECT AVG(ES.SALARY), AVG(ES.COMM)
       FROM EMPLOYEE ES
       WHERE ES.WORKDEPT = EU.WORKDEPT)
       WHERE EU.EMPNO = '000120'

• Example 5:  In a C program display the rows from the EMPLOYEE table and then, if requested to do so, change the job (JOB) of certain employees to the new job keyed in.

     EXEC SQL  DECLARE C1 CURSOR FOR
                    SELECT *
                      FROM EMPLOYEE
                      FOR UPDATE OF JOB;
  
     EXEC SQL  OPEN C1;
  
     EXEC SQL  FETCH C1 INTO ...     ;
     if ( strcmp (change, "YES") == 0 )
       EXEC SQL  UPDATE EMPLOYEE
                   SET JOB = :newjob
                   WHERE CURRENT OF C1;
  
     EXEC SQL  CLOSE C1;

• Example 6:  These examples mutate attributes of column objects.
  Assume that the following types and tables exist:

     CREATE TYPE POINT AS (X INTEGER, Y INTEGER)
       NOT FINAL WITHOUT COMPARISONS
       MODE DB2SQL

     CREATE TYPE CIRCLE AS (RADIUS INTEGER, CENTER POINT)
       NOT FINAL WITHOUT COMPARISONS
       MODE DB2SQL

     CREATE TABLE CIRCLES (ID INTEGER, OWNER VARCHAR(50), C CIRCLE

  The following example updates the CIRCLES table by changing the OWNER column and the RADIUS attribute of the CIRCLE column where the ID is 999:

     UPDATE CIRCLES
       SET OWNER = 'Bruce'
         C..RADIUS = 5
       WHERE ID = 999

  The following example transposes the X and Y coordinates of the center of the circle identified by 999:

     UPDATE CIRCLES
       SET C..CENTER..X = C..CENTER..Y,
         C..CENTER..Y = C..CENTER..X
       WHERE ID = 999

  The following example is another way of writing both of the above statements. This example combines the effects of both of the above examples:

     UPDATE CIRCLES
       SET (OWNER,C..RADIUS,C..CENTER..X,C..CENTER..Y) =
         ('Bruce',5,C..CENTER..Y,C..CENTER..X)
       WHERE ID = 999

• Example 7: Update the XMLDOC column of the DOCUMENTS table with DOCID ‘001’ to the character string that is selected and parsed from the XMLTEXT table.

     UPDATE DOCUMENTS SET XMLDOC =
       (SELECT XMLPARSE(DOCUMENT C1 STRIP WHITESPACE)
         FROM XMLTEXT WHERE TEXTID = '001')
     WHERE DOCID = '001'

https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0001022.html

================================================

REPLACE

>>-REPLACE--(--source-string--,--search-string--+-------------------+--)-><
                                                '-,--replace-string-'      

   SELECT CHAR(REPLACE('DINING','N','VID'),10)
     FROM SYSIBM.SYSDUMMY1;

   SELECT REPLACE('ABCXYZ','ABC','')
     FROM SYSIBM.SYSDUMMY1;

   SELECT REPLACE('ABCCABCC','ABC','AB')
     FROM SYSIBM.SYSDUMMY1;

Since 11 April 2023: 384 total views,  1 views today