DB2 update statements for records in the same table

The following describes the DB2 update statements used to update records in the same table. This DB2 update statement is for your reference and may help you learn about the DB2 update statements.

Two DB2 update statements are used to update other records that meet the conditions in the same table. The data structure is as follows:

Metadata_xt (id, subject_id, other_courseware_name, me_author, me_title, me_key, me_abstract)

Among them, id is unique; me_title should also be unique, but there is no constraint, so it may be repeated, but even if it is repeated, other fields of different records of the same me_title value must be the same, so we can determine redundancy, that is, it can be filtered); others are not unique. All fields are not empty. The statement used for matching is me_title, and its pattern may be as follows:
'Your _ Lite version ',' Your _ Lite version ', and' Your _ 'and! Contains 'lite version ';' _ lecture % 'and! Contains 'lite version '; other modes that do not start with 'sub.

The statement is as follows. The main purpose is to set the me_title to the record 'Number _ speak Lite version 'with the me_title as the same name and start with 'Number _ speak.... The updated record is:
Me_title: me_title + 'lite version) 'of the source record )';
Other fields: the corresponding field value of the source record.

For more information about the function, see:

-- First, update fields other than key items used for matching.
UPDATE DB2ADMIN. metadata_xt AS
SET (me_key, -- DB2 can assign values to multiple fields at the same time in this way. The reason may be related to the values statement,
Me_abstract, -- but not sure, to be verified.
Subject_id,
Me_author
) =
-- The unique record is returned through distinct. The where clause can ensure that a unique me_title value is returned,
-- Because of the possible repeated me_title errors mentioned earlier, make sure that only one record is returned for the same me_title.
(SELECT distinct me_key, me_abstract, SUBJECT_ID, me_author
FROM DB2ADMIN. metadata_xt AS B
WHERE other_courseware_name = 'House scheme' -- group by this field and update a new group of statements each time.
AND B. ME_TITLE like '%' -- ignore other records whose me_title does not start'
AND -- match the me_title mode between two records
(Left (B. ME_TITLE, 6) = left (. ME_TITLE, 6) -- match the first six characters, for example, '2017... 'or' 55th lecture...'
OR -- if both records do not conform to the mode 'Speak % ', the source record should be the mode 'Speak %' and match as follows
(Substr (a. ME_TITLE, 3, 1) = '0' and substr (a. me_title, 4, 3) = substr (B. ME_TITLE, 3, 3 ))
)
AND B. ME_TITLE <> a. ME_TITLE) -- the two fields must have different values to avoid updating the same record.
WHERE other_courseware_name = 'House architect '-- specifies the group for which records are to be updated.
AND me_title LIKE '_' -- this is the record mode to be updated
And exists -- this statement is very important to avoid assigning null values to the corresponding fields of the "Lite" record without the corresponding source record.
(SELECT 1 -- returns 1 only when a matched source record exists.
FROM DB2ADMIN. metadata_xt AS c
WHERE c. OTHER_COURSEWARE_NAME = 'House configuration' -- similarly, the following is exactly the same as the subquery above.
AND c. ME_TITLE like '%'
AND
(Left (c. ME_TITLE, 6) = left (a. ME_TITLE, 6)
OR
(Substr (a. ME_TITLE, 3, 1) = '0' and substr (a. me_title, 4, 3) = substr (c. ME_TITLE, 3, 3 ))
)
AND c. ME_TITLE <> a. ME_TITLE );

-- After updating all other fields, run the command again to update the key item me_title.
UPDATE DB2ADMIN. metadata_xt AS
SET me_title =
(SELECT distinct me_title
FROM DB2ADMIN. metadata_xt AS B
WHERE other_courseware_name = 'House configuration'
AND B. ME_TITLE like '%'
AND
(Left (B. ME_TITLE, 6) = left (a. ME_TITLE, 6)
OR
(Substr (a. ME_TITLE, 3, 1) = '0' and substr (a. me_title, 4, 3) = substr (B. ME_TITLE, 3, 3 ))
)
AND B. ME_TITLE <> a. ME_TITLE) | 'lite version) '-- Key Item modification: Source me_title + 'lite version )'
WHERE other_courseware_name = 'House configuration'
AND me_title LIKE 'your _ Lite version'
AND EXISTS
(SELECT 1
FROM DB2ADMIN. metadata_xt AS c
WHERE c. OTHER_COURSEWARE_NAME = 'House configuration'
AND c. ME_TITLE like '%'
AND
(Left (c. ME_TITLE, 6) = left (a. ME_TITLE, 6)
OR
(Substr (a. ME_TITLE, 3, 1) = '0' and substr (a. me_title, 4, 3) = substr (c. ME_TITLE, 3, 3 ))
)
AND c. ME_TITLE <> a. ME_TITLE );
 

Introduction to DB2 datetime Data Types

Analyze the advantages of the DB2 database

DB2 tablespace Management

DB2 database startup failure Solution

Create a DB2 Management Server