How can I join two table with an exclusion?

Here are the details: I have a table and a view with
identical fields except the table has description (c) and
both have customer(c), po_num (n), and total (n). Both
the table and the view will contain identical records
other than the Desc field in the table. My question is
how can I update the table with records from the view
without overwriting the records in the table or producing
dupilcate records. I am updating about 8,000 records at
a time so duplicate records are out of the question.

Po_Num is the primary key for the table. I have tried
this approach :

SELECT Psc.customer, Psc.po_num, Psc.total,
Psc.ship_code, Ft.po_num;
FROM PSC
WHERE Psc.po_num <> Ft.po_num

but this only filters out 1 record set. I have tried
using a scan and endscan but it is only moving one record
pointer still only filtering one record set.

any fresh ideas?

woodie

Cindy
Thu Sep 18 18:37:05 CDT 2003

Hi Woodie,

I'm not sure I understand, but it sounds like you want to get records from
the view that are not already in the table and add them to the table. You
can do this by:

SELECT * FROM MyView ;
WHERE Po_Num NOT IN ;
(SELECT Po_Num FROM MyTable) ;
INTO CURSOR NewRecords
SELECT MyTable
APPEND FROM DBF("NewRecords")

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy.winegarden@mvps.org, www.cindywinegarden.com

"Woodie" <woodie.westbrook@knothe.com> wrote in message
news:05dc01c37e1d$4b208680$a101280a@phx.gbl...
> Here are the details: I have a table and a view with
> identical fields except the table has description (c) and
> both have customer(c), po_num (n), and total (n). Both
> the table and the view will contain identical records
> other than the Desc field in the table. My question is
> how can I update the table with records from the view
> without overwriting the records in the table or producing
> dupilcate records. I am updating about 8,000 records at
> a time so duplicate records are out of the question.
>
> Po_Num is the primary key for the table. I have tried
> this approach :
>
> SELECT Psc.customer, Psc.po_num, Psc.total,
> Psc.ship_code, Ft.po_num;
> FROM PSC
> WHERE Psc.po_num <> Ft.po_num
>
> but this only filters out 1 record set. I have tried
> using a scan and endscan but it is only moving one record
> pointer still only filtering one record set.
>
> any fresh ideas?
>
> woodie
>