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
Note
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';Note
The only valid value for the TCPART parameter is Y. You cannot convert tables from table-controlled partitioning to index-controlled partitioning.
Related topic
