RE: Your ideas will be helpful

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 01 Aug 2001 13:39:06 -0700
Message-ID: <F001.0035CB4D.20010801133836@fatcity.com>

Answer (original question below)

insert into schema2.table2 (asset_num, company_id, consoli_id, ...) select a.asset_num, a.company_id, consoli_id, ... from schema1.table1 a
where (a.asset_num,

       decode (substr (a.company_id, 1, 1),
               'D', 1, 'A', 2, 'C', 3, 4

              )

      ) = (select

              b.asset_num,
              min (decode (substr (b.company_id, 1, 1),

                           'D', 1, 'A', 2, 'C', 3, 4
                          )
                  )
           from schema1.table1 b
           where b.asset_num = a.asset_num

           group by b.asset_num
          ) ;

Jacques R. Kilchoër
x8816

-----Original Message-----
From: Helen rwulfjeq [mailto:lannyue_at_yahoo.com]

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) Received on Wed Aug 01 2001 - 15:39:06 CDT