Archives of the TeradataForum

Message Posted: Wed, 06 Feb 2002 @ 16:27:17 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Recently I have had many discussions with co-workers on the subject of running an UPDATE to a table using data from another table. Most of ourtables have more than 1,000,000 rows and they are trying to update anywhere from 25% to 75% of the data rows are between 2 to 5 fields actually being updated. If we run an UPDATE, such as:

UPDATE Table1
SET Col1 = 'a',
        Col2 = 34
WHERE Col3 = 'Apples';

We encounter no major issues. The UPDATE eventually finishes and everyone is happy. If we instead say something like this:

UPDATE Table1
SET Col1 = Table2.Col4,
        Col2 = Table2.Col5
WHERE Col3 = 'Apples'
AND Table1.Col6 = Table2.Col6;

The query never finishes or will run for hours or days and then we abort it, etc. I understand UPDATEs use overhead (Transient Journal) and our little two node 4850 may not be powerful enough to handle UPDATEs. I have heard from some NCR/Teradata folks that we should not update more than 10,000 records at a time. I am convinced we will have to run more DELETE/INSERT processing and get away from UPDATEs, however, I need more information on the subject as to other methods or better practices.

Thank you for all your time and help on this subject in advance.

Matthew Lipke

	<Prev	Next>		<<First	<Prev	Next>	Last>>