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:[EMAIL PROTECTED]]
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)
