Archives of the TeradataForum
Message Posted: Thu, 17 Sep 2009 @ 13:42:25 GMT
| Subj: | | Re: Update of millions of records |
| |
| From: | | Jonathan Downs |
| | I have to do an update some millions of records in a table by matching records from another table. So, which one will be better among
these: | |
| | 1. Direct update to the table through SQL | |
| | 2. Or first we will export from source table and again update the target table doing multiload of the exported file. | |
Re. 1 - an absolute no no - whilst Teradata will do it, it really isn't the best use of machine resource (CPU).
Re. 2 - even more of a no no - why cause IO when you have all the data on the Teradata??
So I believe that the answer is neither - seriously consider joining the two tables and put the output in a new (empty) table which has the
same layout as the table you wanted to update - effectively applying the update logic in the select list. Once the insert has occurred rename the
input table to a temp name (ie move itd to one side) and the output to be the same as the original table - honestly this is fast!
If you know the nature of the data and it is unlikely to produces more rows then consider using multiset on the table that you're inserting
into as this removes the duplicate row checking.
Cheers
Jon
| 
