Drop record from a table

[#1]

Hi guys,

Which is the fast way to drop record from a table, starting from an "exclusion table".

I have let's say 'table1' of all my record, but I've to exclude those record that I've in 'ExclusionTable'

Matching field are 2. So which is in your opinion the fastest way to exclude record of 'Exclusion Table' from 'Table1'

Thanks

Sergio

[johnw]

The fastest way is probably to not load the excluded rows in the first place:

[Exclusion Table]:
LOAD Field1 & ',' & Field2 as ExclusionKey
...
;
[Main Table]
LOAD
UniqueID
,some fields
,Field1
,Field2
,some other fields
...
WHERE NOT EXISTS (ExclusionKey,Field1 & ',' & Field2)
;

If you MUST load the main table first (or if using QVDs, where the not exists above would, I believe, cause an unoptimized load), probably something like this:

[Main Table]
LOAD
UniqueID
,some fields
,Field1
,Field2
,some other fields
...
;
[Exclusion Table]:
LOAD Field1 & ',' & Field2 as ExclusionKey
...
;
INNER JOIN ([Main Table])
LOAD UniqueID
RESIDENT [Main Table]
WHERE NOT EXISTS (ExclusionKey,Field1 & ',' & Field2)
;