
上QQ阅读APP看书,第一时间看更新
There's more...
We not only have other alternatives to the update statement, but we can also write an update as an UPSERT, meaning, it executes an INSERT if not and performs the UPDATE if the row exists:
MERGE INTO
Table1 AS t1
USING
Table1_this AS t2
ON
t1.PK_Col=t2.PK_Col
WHEN MATCHED THEN UPDATE
SET
Col1=t1.Col1,
Col2=t1.Col2
WHEN NOT MATCHED THEN
INSERT
(
t1.PK_Col,
t1.Col1,
t1.Col2
)
;
You can update the data in a table not only using the update statement but using three other alternatives:
- MERGE INTO: Merges a source row set into a target table based on whether any target rows satisfy a specified matching condition with the source row. The MERGE statement combines the INSERT and UPDATE statements into a single conditional statement.
What makes MERGE conditional is its two test clauses, called WHEN MATCHED and WHEN NOT MATCHED:

The step does a RowKey-based merge join internally, identifying source rows that qualify for updating target rows and
- Delete.Insert: This requires TJ space for each row until the transaction ends. This strategy is not optimal but can be efficient if you are updating a large number of rows with PI columns and no PPI is present on the target table. It is also the good choice when you have JI on the tables and you can't afford to drop and recreate them.
- Insert.Update, also called UPSERT: When the update operation fails, the INSERT statement executes, per upsert feature. Each record contains the primary key value of a row that is to be inserted successively into the target table whose columns are PI.
UPSERT on a PPI table: The INSERT portion must specify the same partition as the UPDATE portion.
The UPDATE must not modify any of the partitioning columns.
All values of the partitioning columns must be specified in the WHERE clause of the UPDATE portion.
The UPDATE must not modify any of the partitioning columns.
All values of the partitioning columns must be specified in the WHERE clause of the UPDATE portion.
- Multiload update: Replaces the whole block at a time, which helps in reducing I/O. It requires all transactions to be primary index operations when using the Update operator. UPSERT can also be performed using this. Also, it helps in TJ rollback as it has restart capabilities.
Failure, 3538: A MultiLoad UPDATE Statement is Invalid: Indicates that an UPDATE statement in a Multiload task that does not fully specify a primary index value. Use = for PI.</span>
Based on your requirements and scenario, you need to choose which is the best strategy for you to update the rows.