How it works...

As we understand it, update is used to alter or change the values of existing rows of a table. The updating of the rows can be based on some where condition or full table if needed. You can use sub queries in update statements.

There is much to consider when it comes to updating rows in the table:

• Index-based update: Updating primary columns of the table if it is a very slow and high CPU/IO intensive job. Try to avoid it at all costs.
• Abort update: Huge update abort results in TJ. Avoid aborting long-running updates.
• SI/JI: If you find your update stuck at all-AMPs UPDATE in explain, check secondary or join indexes on the updating table. Drop them before updating and recreate them.
• SET and updating column: Always verify that columns that you are trying to update, which are in SET command, are not in your qualifier where clause.
• NoPI: SQL UPDATE requests cannot update a NoPI target table. 
• PPI considerations: You need to reconsider the partition column when updating a table with the PPI column. The PPI helps in reducing I/O and also avoids full table scans. Not using the PPI results in performance degradation of the query.