> Is this SQL Server?
MySQL/PHP
> How many fields are you comparing to in the master
table?
Only one field in both master and test tables. Potentially
22,000 records
in master and perhaps double that (not de-duped) in the test.
> Hope that helps.
I would say it has solved the problem! I wanted substantive,
and I got a
solution. Thanks, Tom.
When you say 'nothing for SQL' do you mean milliseconds?
--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.dreamweavermx-templates.com
- Template Triage!
http://www.projectseven.com/go
- DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs,
Tutorials & Resources
http://www.macromedia.com/support/search/
- Macromedia (MM) Technotes
==================
"Tom Muck" <tommuck@NOSPAM.basic-drumbeat.com> wrote in
message
news:ee97kg$hm5$1@forums.macromedia.com...
> Is this SQL Server? How many fields are you comparing to
in the master
> table? If only one, I would import the data, eliminate
duplicates, then
> compare to the data field in the master table. I usually
eliminate dupes
> using a temp table, although there are many other ways
to do it:
>
> select distinct yourfield into #holding from yourtable
> truncate table yourtable
> insert yourtable select yourfield from #holding
> drop table #holding
>
> Then get data that does not match the master:
>
> SELECT yourfield FROM yourtable WHERE yourfield NOT IN
(SELECT yourfield
> from mastertable)
>
> That gives you all data not in the master table. If you
want it inserted,
> write an insert statement:
>
> INSERT master (yourfield)
> SELECT yourfield FROM yourtable WHERE yourfield NOT IN
(SELECT yourfield
> from mastertable)
>
> It could all be done in one query, but this gives you
the inbetween steps
> and tables. 20K records is nothing for SQL.
>
> For percentage overlap, get counts of each:
>
> SELECT COUNT (*) as NewRecordCount FROM yourtable
> SELECT COUNT(*) as Matches FROM mastertable WHERE
yourfield IN (select
> yourfield from yourtable)
> SELECT COUNT(*) as NonMatchesMaster FROM mastertable
WHERE yourfield NOT
> IN (select yourfield from yourtable)
> SELECT COUNT(*) as NonMatchesNew FROM yourtable WHERE
yourfield NOT IN
> (select yourfield from mastertable)
>
> Hope that helps.
> --
> Tom Muck
> co-author Dreamweaver MX 2004: The Complete Reference
>
http://www.tom-muck.com/
>
> Cartweaver Development Team
>
http://www.cartweaver.com
>
> Extending Knowledge Daily
>
http://www.communitymx.com/
> "Murray *ACE*" <forums@HAHAgreat-web-sights.com>
wrote in message
> news:ee7jud$gqh$1@forums.macromedia.com...
>>I have a job that will involve comparing data in an
uploaded file to a
>>master data set, looking for indenties.
>>
>> The incoming file will be a numeric only CSV, and
may contain 20,000
>> records, each with a single large integer field. I
want to a) eliminate
>> dupes from that incoming data, and then compare the
de-duped list to the
>> master list to calculate % overlap, or identity.
>>
>> I'm thinking I'd read the incoming data into a
table, then select unique
>> from the table, and begin a brute force comparison,
field by field,
>> scanning each field against the master until I reach
a master value that
>> is larger than the test value, and then moving on to
the next test
>> record.
>>
>> Is there a SQL way of doing this more efficiently?
>>
>> --
>> Murray --- ICQ 71997575
>> Adobe Community Expert
>> (If you *MUST* email me, don't LAUGH when you do
so!)
>> ==================
>>
http://www.dreamweavermx-templates.com
- Template Triage!
>>
http://www.projectseven.com/go
- DW FAQs, Tutorials & Resources
>>
http://www.dwfaq.com - DW FAQs,
Tutorials & Resources
>>
http://www.macromedia.com/support/search/
- Macromedia (MM) Technotes
>> ==================
>>
>>
>>
>
>
6
Replies
AdChoices