The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Scribe Delete
Posted: 20 Aug 07 3:53 PM
|
| Could someone please point me in the right direction for deleting target records that are no longer in the source? Thanks! |
|
|
|
Re: Scribe Delete
Posted: 20 Aug 07 4:24 PM
|
If you are updating the existing records based on the source and deleting target records that do not exist in the source, I suggest purging the target table and reloading it. If the source is always the "winner" this is the simplest and likely the fastest way to approach the problem.
Timmus |
|
|
|
Re: Scribe Delete
Posted: 20 Aug 07 10:31 PM
|
True, but depending on data volumes, this might generate a lot of unwanted sync traffic when the data is reloaded.
I'm no Scribe expert, so I can't offer an alternative solution - just mentioning a possible concern with the purge/recreate method.
PP |
|
|
|
Re: Scribe Delete
Posted: 21 Aug 07 12:55 AM
|
Absolutely. However, in the case that every row is going to be updated, I believe the sync traffic would be nearly the same as the insert traffic. Might as well keep the solution simple if possible. Of course, the assumption that every source row is the "winner" is just that: my assumption 
Timmus |
|
|
|
Re: Scribe Delete
Posted: 21 Aug 07 7:50 AM
|
The first step is to identify the rows to be deleted. I do this by creating what MS Access calls a no match query. Try something like this:
SELECT t.TargetTableID FROM SalesLogix.sysdba.TargetTable t
LEFT JOIN SourceDB.Owner.SourceTable s
ON t.TargetTableID = s.SourceTableID
WHERE s.SourceTableID IS NULL
There are several variation on the syntax to get the same results. Here the LEFT JOIN returns all the records from the TargetTable even if there is not a matching ID record in the Source. The WHERE clause then filters the records to only return the ID's that do not have a matching ID in the source table. |
|
|
|
Re: Scribe Delete
Posted: 21 Aug 07 8:37 AM
|
| Thanks for the replies! Yes, the source is the winner. Unfortunately volume is a concern in a couple of cases, I'm dealing with a providex source. I'll try both methods, wiping the table clean/reloading and the no match query. Thanks again for the replies. |
|
|
|