CODEWITHSUNDEEP
sqlteradata
access_granted
access_granted

Reputation: 1927

update of full table fails on a specific column with "duplicate row error"

This works:

update table_name
set column1=trunc(column1,3);

This doesn't:

update table_name
set column2=trunc(column2,3);

Neither column is a unique or primary key. Table struct is:

CREATE SET TABLE TABLE_NAME ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      KEYCOL NUMBER,
      COLUMN1 FLOAT,
      COLUMN2 FLOAT
)
PRIMARY INDEX ( KEYCOL );

Upvotes: 0

Views: 1307

Answers (1)

Rob Paller
Rob Paller

Reputation: 7786

By default in Teradata session mode, tables that do not include the SET or MULTISET option will be created in SET mode. This means that Teradata will enforce duplicate row checks on the table in the absence of a UNIQUE constraint, such as a UNIQUE PRIMARY INDEX or UNIQUE SECONDARY INDEX.

In ANSI session mode, the default behavior is the opposite. Teradata will create a MULTISET table which does permit duplicate rows and eliminates the overhead of duplicate row checking when you have a non-unique primary index.

Can you provide the SHOW TABLE output to verify how the table is structured? Your DDL statement that you submitted leaves out some of the default options that are included in the table creation, including SET vs. MULTISET, FALLBACK vs. NO FALLBACK, etc.

edit After reviewing your updated table definition, the SET option is the reason you are getting the error. This could be a good thing or bad thing depending on your intent and your tolerance for duplicate rows in your table.

To eliminate the error you either have to rebuild the table as a multi set table or reconsider the update your are applying and the consequences it has on the data in your table. As this is an unconstrained update, you may encounter addition records where this error would occur.

You could run a SELECT statement against the table to try to identify how many times your update would produce a duplicate row. You would probably have to group by every column in the table, replacing the column being updated with the function being applied having COUNT(*) > 1.

Upvotes: 4

Related Questions