Archives of the TeradataForum

Message Posted: Tue, 18 Dec 2012 @ 20:05:09 GMT

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

<-- Anonymously Posted: Tuesday, December 18, 2012 11:02 -->

Hi all

I have a table with a significant number of rows in it and an "exceptions" table with a tiny number of rows in it. The exceptions data is a subset of the large table data.

The table structures are identical. The tables have a very large number of columns, many of which are nullable and (due to rogue data coming from source) the natural key for the data is a combination of every column.

The query

     sel * from large_table
     intersect
     sel * from exceptions_table

returns the exact number of rows on the exceptions table.

I would like to delete the rows from the large table which exist in the exceptions table.

I know I could write the DELETE query with an inner join between the 2 tables on every column, using COALESCE to assist with Null processing.

I know I could insert into an empty temp table all large table rows minus all small table rows, but this is moving a large amount of data for the sake of removing a tiny amount of data.

I know the data could be fixed at source thus eliminating the problem altogether and this is being addressed, however I was hoping to code something very basic using EXISTS or INTERSECT or something without having to reference the full column list in the delete statement E.g.

     delete from large_table
     intersect
     exceptions_table;

Is there such an SQL construct ?

thanks in advance for any help

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