The UPDATE statement changes data structures.
You can import the UPDATE statement in a DML file. For information, see Overview-of-importing-files.
UPDATE objectType
Specifies the object type to be updated. The objectType variable can be one of the following keywords:
- ALIASES
- CHECKS
- COLUMNS
- CONTROLS
- DATABASES
- FIELDS
- FOREIGNKEYS
- INDEXES
- INDEXPARTS
- KEYCOLUMNS
- MASKS
- PARMS
- PERMISSIONS
- RELS
- ROUTINES
- SEQUENCES
- STOGROUPS
- SYNONYMS
- TABLEPARTS
- TABLES
- TABLESPACES
- TRIGGERS
- VIEWS
- VOLUMES
Important
The keywords that are listed are created by the CM/PILOT component when it builds a DML statement. When modifying a DML statement, you can use the keywords as they are shown, singular forms of the keywords, or the actual Db2 catalog names.
SET attribute = value
For details of the SET clause, see SET-clause.
WHERE searchCondition
Specifies the criteria that you use to define the scope for the action.
For details of the WHERE clause, see WHERE-clause.
Examples
The following examples illustrate the use of the UPDATE statement. For additional examples and tasks, see Accomplishing-your-data-structure-modification-goals.
Excluding NULL rows from indexes
Example
The following example illustrates how to exclude null rows from non-unique indexes.
UPDATE INDEXES
SET SPARSE = 'X'
WHERE IX_EXTENSION_TYPE IN ('V','S');
Updating check constraints
Example
The following example illustrates how to update a check constraint.
UPDATE CHECKS
SET CHECKCONDITION='(FO > 1.1E+1299999999)'
WHERE CHECKNAME='FO' AND
TBNAME='TBN3B' AND
TBOWNER='ALU076';
Updating columns
Example
The following example illustrates how to change the length of table and view columns that are named ZIPCODE and that are 5 to 9 characters in length.
UPDATE COLUMNS
SET LENGTH = 9
WHERE NAME = 'ZIPCODE' AND LENGTH = 5;
Updating databases
Example
The following example illustrates how to change the storage group of databases that currently use the OLDGROUP storage group and that have at least one table space that has over 100 active pages. The new storage group for the databases is named NEWGROUP.
UPDATE DATABASES
SET STGROUP = 'NEWGROUP'
WHERE STGROUP = 'OLDGROUP' AND
EXISTS (SELECT * FROM SYSIBM.SYSTABLESPACE
WHERE DBNAME = SYSIBM.SYSDATABASE.NAME AND
NACTIVE > 100);
Updating space allocation based on statistics
Example
The following example illustrates how to change the primary quantity for the partitions of a table space in which the amount of space that rows of data from active tables occupy is greater than 50 percent.
UPDATE TABLEPARTS
SET PQTY = PQTY * 2
WHERE PERCACTIVE > 50;
Updating synonyms
Example
The following example illustrates how to change the names of synonyms. In this example, the prefix of TEST is changed to PROD.
UPDATE SYNONYMS
SET NAME = 'PROD' CONCAT SUBSTR(NAME,5)
WHERE NAME LIKE 'TEST%';
Updating tables
Example
The following example illustrates how to change the creator of all of the tables in a database.
UPDATE TABLES
SET CREATOR = 'NEWCREATOR'
WHERE DBNAME = 'ABCD';
Example
The following example illustrates how to change the names of tables that begin with WMS to names that begin with MJF.
UPDATE TABLES
SET NAME = 'MJF' CONCAT SUBSTR(NAME,4)
WHERE NAME LIKE 'WMS%';
Changing explicitly created tables to implicitly created tables
Example
The following example illustrates how to move tables from explicitly created databases to implicitly created databases. The product performs the following actions:
- Drops the tables, and creates the tables in implicitly created table spaces and implicitly created databases
- Changes the names of the table space and database to <DEFLT>
- Changes partitioned indexes to non-partitioned indexes
UPDATE TABLES
SET DBNAME = '<deflt>'
WHERE DBNAME LIKE 'CRJICP%';
Converting tables from index-controlled partitioning to table-controlled partitioning
Example
The following example illustrates how to convert several tables from index-controlled partitioning to table-controlled partitioning by using the TCPART parameter. The TCPART parameter can also be used in the LIKE TABLES and MIGRATE TABLES statements.
UPDATE TABLES
SET TCPART = 'Y'
WHERE CREATOR = 'CRJICP';
Important
The only valid value for the TCPART parameter is Y. You cannot convert tables from table-controlled partitioning to index-controlled partitioning.
