Archives of the TeradataForum
Message Posted: Tue, 18 Dec 2012 @ 20:05:09 GMT
<-- Anonymously Posted: Tuesday, December 18, 2012 11:02 -->
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.
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
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|