Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> want to insert all columns while preserving uniqueness on some
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