UPDATE Command Teradata

The UPDATE statement is used to modify data values in one or more columns of one or more existing rows. A status is the only returned value from the database; no rows are returned to the user. In a data warehouse environment, it is not normally a heavily used SQL command. That is because it changes data stored within a row and much of that data is historic in nature. Therefore, history is not normally changed.

However, when business requirements call for a change to be made in the existing data, then the UPDATE is the SQL statement to use. In order for the UPDATE to work, it must know a few things about the data row(s) involved. Like all SQL, it must know which table to use for making the change, which column or columns to change and the change to make within the data.

For privileges, the user issuing the UPDATE command needs UPDATE privilege against the table or database in order for it to work. Additionally, the UPDATE privilege can be set at the column level. Therefore, the ability to modify data can be controlled at that level.

The basic syntax for the UPDATE statement follows:

Note Using UPD instead of UPDATE is not ANSI compliant

The UPDATE can be executed interactively when all of the new data values are known ahead of time. However, when the data is being imported from an external source, using BTEQ, TPump, FastLoad, or MultiLoad with the data values being substituted from a record in a file. Additionally, the UPDATE command can modify all or some of the rows in a table using a mathematics algorithm against the current data to increase or decrease it accordingly.

An example of each of these types follows. The first UPDATE command modifies all rows that contain ‘My character data’ including the one that was inserted earlier in this chapter. It changes the values in three columns with new data values provided after the equal sign (=):

UPDATE My_table SET Column2 = 256 , Column4 = 'Mine', Column5 = 'Yours' WHERE Column1 = 'My character data' ;

The next UPDATE uses the same table as the above statement. However, this time it modifies the value in a column based on its current value and adds 256 to it. The UPDATE determines which row(s) to modify with compound conditions written in the WHERE clause based on values stored in other columns:

Sometimes it is necessary to update rows in a table when they match rows in another table. To accomplish this, the tables must have one or more columns in the same domain. The matching process then involves either a subquery or join processing.

The subquery syntax for the UPDATE statement follows:

Let's see this technique in action. To change rows in My_table using another table called Ctl_tbl, the following UPDATE uses a subquery operation to accomplish the operation:

The join syntax for the UPDATE statement follows:

Note When adding an alias to the UPDATE, the alias becomes the table name and MUST be used in the WHERE clause when qualifying columns.

To change rows in My_table using another table called Ctl_tbl the following UPDATE uses a join to accomplish the operation:

In reality, the FROM is optional. This is because Teradata can dynamically include a table by qualifying the join column with the table name. The FROM is only needed to make an alias for the join tables.

The next UPDATE is the same as the above without the FROM for Ctl_tbl:

Additionally, when you use the FROM, a derived table may be used for the join.

Fast Path UPDATE

The UPDATE command is the only DML that starts with a row, modifies the row and rewrites the row to the table. Therefore, it cannot start nor end with an empty table. As a result, there really isn't such a thing as a Fast Path UPDATE.

However, the database can be tricked into doing a fast UPDATE. To accomplish this, the INSERT/SELECT may be used. However, instead of selecting the rows as they currently exist, the change or update is made during the SELECT portion of the INSERT/SELECT into an empty table.

The following INSERT/SELECT "updates" the values in Column3 and Column5 in every row of My_table, using My_Table_Copy via BTEQ:

Column1 ,Column2 ,Column3*1.05 ,Column4
,'A' ,Column6 FROM My_Table ;

When the above command finishes, My_Table_Copy contains every row from My_Table with the needed update. Next, all of the rows must be deleted from My_Table and a second Fast Path INSERT/SELECT puts all the rows back into My_table. Otherwise, My_Table can be dropped and My_Table_Copy renamed to My_Table. Depending on the table size this may be the fastest solution.

The above combination of these statements will be very fast. However, caution must be exercised so that another user does not make a different change to any rows in My_Table before it is dropped and the rows deleted. If this should happen, that change will be lost.

Later in this chapter, the concept and methods to create a multi-step "transaction" will be explained.

When the UPDATE modifies less than all of the rows, the above Fast Path operation cannot be used. There is an alternative that requires a second SELECT:

Since the two SELECT operations can be done in parallel and then combined, they both execute fairly fast, but spool is required. Then, the results are combined and inserted into My_Table_Copy.

Like the previous Fast Path, extra space is needed for a period of time, because the rows in the original table must be dropped and the new copy must be renamed to the same name as the original table. Otherwise, the rows of the original table are deleted and copied back from the updated copy. Additionally, all this must occur without any other update operations occurring on the original table.

It is fast, but there are many considerations to take into account. It may not be Nirvana in all cases. Like all tools, use them responsibly.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics