SQL statement - Thread 184-1094456

[keepingbusy]

Hi

Regarding Mike Lewis' answer to a problem on thread184-1094456, I have used this to accomplish and overcome a problem we had with new titles in two tables.

The two tables are called UPCONLY and TEXTWEB.

The following statement looks at the table UPCONLY (field UPCOLD) and compares it to TEXTWEB (field COLUMN5) to see if there are any new UPC numbers in the new table, not already in the old table (This is an automated process that later creates CSV files for upload via phpmyadmin to a website.

SELECT * FROM TEXTWEB ;
  where COLUMN5 NOT IN ;
  (SELECT UPCOLD FROM UPCONLY);
  INTO TABLE tempfile+'.dbf'

Some files can be over 200,000 titles so I am looking for a way to reverse the process in that another table can store titles that are in the new table that are no longer on the old.

Because of the amount of records, it's quite difficult to see if the below works:

SELECT * FROM UPCONLY ;
  WHERE UPCOLD NOT IN ;
  (SELECT COLUMN5 FROM TEXTWEB) ;
  INTO TABLE TEMP.DBF

Can anyone suggest if the second code is correct to achieve my task?

Many thanks
Lee

Windows XP
Visual FoxPro Version 6 & 9

http://www.customsoftware.co.uk

 

[bborissov]

I prefer to use JOIN NOT is and derived tables. if you have proper indexes query will be very fast:

SELECT UPCONLY.*;
       FROM UPCONLY ;
LEFT JOIN TEXTWEB ON UPCONLY.UPCOLD = TEXTWEB.COLUMN5;
WHERE TEXTWEB.COLUMN5 IS NULL;
INTO ....

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[keepingbusy]

Hi Borislav

Thank you for the prompt reply. I have run a test and it shows about 7000 records that appear to meet the criteria. I'll run with that and see how we get on.

Thanks again
Lee

Windows XP
Visual FoxPro Version 6 & 9

http://www.customsoftware.co.uk