Re: want to insert all columns while preserving uniqueness on some

dan wrote:
> 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
>

Have a look at the MERGE statement. This SQL statement, available in 9i and higher, will let you insert and update data in the same SQL statement.

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown