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