Re: Your ideas will be helpful

[Jon Walthour]

Helen:   How about a select statement that will do it for you instead of all the looping? Does this work?   select   asset_num        , decode(min(num), 1, 'DHI', 2, 'AHI', 3, 'CHI', '???') as company_id     from (select asset_num                , decode(company_id, 'DHI', 1, 'AHI',2, 'CHI', 3, 4) as num             from assets) group by asset_num;   Hope this helps.   Jon Walthour ----- Original Message ----- From: Helen rwulfjeq To: Multiple recipients of list ORACLE-L Sent: Wednesday, August 01, 2001 5:12 PM Subject: Your ideas will be helpful  Hello, all: I'm import data from schema1.table1 (ASSET_NUM,COMPANY_ID,CONSOLI_ID……) to schema2.table2 (ASSET_NUM,COMPANY_ID,CONSOLI_ID……)using SQL script on Oracle 8i. The situation is like this: In schema1.table1, it does not has a Primary Key and Table1.ASSET_NUM has duplicated records while values in COMPANY_ID are different. For example: ASSET_NUM    COMPANY_ID ----------   ---------- AA237334       AHI AA237334       DHI On the other hand, in schema2.table2, the corresponding column Table2.ASSET_NUM is defined as a NOT NULL, Primary Key. These tables' definition can't be modified. So, I need to drop the duplicated ASSET_NUM/records from schema1.table1 and then import into schema2.table2. The rule of drop will depend on the priorities of COMPANY_ID (ranking as < Dxx, Axx, Cxx >, from < Keep to Drop >. which means between Axx & Dxx, "Dxx" will be imported. For instances, like the above example, record contains 'AHI' will be dropped. This rule is only depended on the first Character -- substr(company_id, 1,1). ) I thought about: 1/ loop a cursor on whole table1? 2/ just duplicate records and then the rest records will be "insert into table2" directly. Then how do I decide which record to drop? Use a second cursor? 3/ screen all the duplicate record into a temp table and create a PK on that table and then decide… (this sounds very redundant) Any ideas how can I do this? Thanks in advance Helen Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/