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

The REPLACE function replaces all occurrences of search-string in source-string with replace-string. If search-string is not found in source-string, source-string is returned unchanged.

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

The schema is SYSIBM.

source-string
An expression that specifies the source string. The expression must return a value that is a built-in character string, graphic string, or binary string data type that is not a LOB and it cannot be an empty string.

Start of changeThe argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.End of change

search-string
An expression that specifies the string to be removed from the source string. The expression must return a value that is a built-in character string, graphic string, or binary string data type that is not a LOB; the value cannot be an empty string.

Start of changeThe argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.End of change

replace-string
Start of changeAn expression that specifies the replacement string. The expression must return a value that is a built-in character string, graphic string, or binary string data type that is not a LOB.

Start of changeThe argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.End of change

If replace-string is not specified or is an empty string, nothing replaces the string that is removed from the source string.End of change

The actual length of each string must be 32704 bytes or less for character and binary strings or 16352 or less for graphic strings.

All three arguments must have compatible data types. If the expressions have different CCSID sets, then the expressions are converted to the CCSID set of source-string.

The data type of the result of the function depends on the data type of source-string, search-string, and replace-string:

  • VARCHAR if source-string is a character string. The encoding scheme of the result is the same as source-string. The CCSID of the result depends on the arguments

The length attribute of the result depends on the arguments:

  • If the length attribute of replace-string is less than or equal to the length attribute of search-string, the length attribute of the result is the length attribute of source-string.
  • If the length attribute of replace-string is greater than the length attribute of search-string, the length attribute of the result is determined as follows depending on the data type of the result:

The result can be null; if any argument is null, the result is the null value.

Example 1: Replace all occurrences of the character ‘N’ in the string ‘DINING’ with ‘VID’. Use the CHAR function to limit the output to 10 bytes.

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

The result is the string ‘DIVIDIVIDG’.

Example 2: Replace string ‘ABC’ in the string ‘ABCXYZ’ with nothing, which is the same as removing ‘ABC’ from the string.

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

The result is the string ‘XYZ’.

Example 3: Replace string ‘ABC’ in the string ‘ABCCABCC’ with ‘AB’. This example illustrates that the result can still contain the string that is to be replaced (in this case, ‘ABC’) because all occurrences of the string to be replaced are identified prior to any replacement.

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

The result is the string ‘ABCABC’.

https://www-01.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_bif_replace.html
=====================================================================

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