Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> want to insert all columns while preserving uniqueness on some

want to insert all columns while preserving uniqueness on some

From: dan <dan.herrick_at_pbs.proquest.com>
Date: 24 May 2006 07:07:00 -0700
Message-ID: <1148479620.429848.277170@g10g2000cwb.googlegroups.com>


There's a uniqueness constraint on the columns listed in this insert statement:

insert into illustration (country_code, catalog_code, art_id,

                                first_year, last_year, country_lang)
       (select distinct cl.country_code,
               b.catalog, a.art_id, b.first_year, b.last_year, 'EN'
          from art a, archcat_caption b, catalog c,
               (select distinct country_code from country_language
                 where country_code in ('US', 'CA')) cl
         where rtrim (rpad (a.art_nbr, 8, ' ')||a.art_suffix) =
                rtrim (b.art_num)
           and b.catalog = c.catalog_code
         minus (select country_code, catalog_code, art_id,
                       to_char (first_year), to_char (last_year),
                       country_lang
                  from illustration
                 where country_lang = 'EN'));

and the "select distinct" and the "minus" are both necessary to avoid violating it. But what I really want to do is copy column b.cap_text to illustration.illustration_desc in the same pass over the data. The only solution I have is to make a second, update, pass over the data which typically runs an order of magnitude slower. (This is just the example I'm working on right now, I have several applications for a solution to this problem.)

dan Received on Wed May 24 2006 - 09:07:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US