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.
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.
The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.
- 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.
The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.
- replace-string
An 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.
The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.
If replace-string is not specified or is an empty string, nothing replaces the string that is removed from the source string.
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.
SELECT CHAR(REPLACE('DINING','N','VID'),10)
FROM SYSIBM.SYSDUMMY1;
The result is the string ‘DIVIDIVIDG’.
SELECT REPLACE('ABCXYZ','ABC','')
FROM SYSIBM.SYSDUMMY1;
The result is the string ‘XYZ’.
SELECT REPLACE('ABCCABCC','ABC','AB')
FROM SYSIBM.SYSDUMMY1;
The result is the string ‘ABCABC’.
Since 11 April 2023: 384 total views, 1 views today