We've got a sloppy database that we must import to from another sloppy
database. The slop is due to user data entry and process inconsistencies
- that can't be fixed here.
The query we need to do (loosely) is this:
insert into table B
select SPO, Field2, Field3, etc... from table A
where A.SPOMangle1 not in (select SPO from table B) and
A.SPOMangle2 not in (select SPO from table B) and
A.SPOMangle3 not in (select SPO from table B) and
...
If you get the idea - the problem is that any of the incoming order
records from table A might have already been either coded or mangled in
a particular way by the users of the system and we must avoid importing
any duplicate records - so we must search for all of the possible
mangled versions of the SPO (ticket number) and exclude those records
from our import.
Another reason for this craziness is what we call "over sampling" the
import data to account for temporary communications failures in the
incoming stream - That is, we use a sample of the incoming data that is
certain to overlap with data that is already in the database by a number
of days so that missing systems will be automatically recovered once
they rejoin the conversation.
We create the import table A from a utility that captures incoming data
from a POS system and creates the possible mangled versions of the SPO
for convenience in this process.
We're currently doing this on MS*SQL - but we hate that and want to move
to MySQL. (Lots of good reasons - replication, speed, and cost among
them.)
We would like to avoid an interactive process if we can.
Since MySQL doesn't do sub selects - how would we work around this?
Thanks!
_M
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
