It is rare that I would recommend large-scale updates of data. This is because updates are usually a very expensive operation from a resource perspective. Often is is more efficient to create a new version of the table and rename it back to the original. For example,
- Code: Select all
create clearance_stg_temp as
select clearance_03ago AS clearance_04ago,
clearance_02ago AS clearance_03ago,
clearance_01ago AS clearance_02ago,
clearance AS clearance_01ago
drop table clearance_stg;
rename clearance_stg_temp to clearance_stg;
I'm sure you have other columns to consider, not sent in the example, but you get the idea.
The CTAS method is often the best solution to any update or delete on a large table.