Re: Help: 8i merge/upsert/insert/update one table from another.

On 21 Mar 2007 09:44:34 -0700, theoraclenewbie_at_gmail.com wrote:

>On Mar 21, 7:50 am, "sybrandb" <sybra..._at_gmail.com> wrote:
>> On Mar 21, 1:34 pm, theoraclenew..._at_gmail.com wrote:
>>
>>
>>
>>
>>
>> > Note: I am a beginner. We currently use Oracle 8i at work.
>>
>> > I am trying to update table REG with the data from table REGTEMP. I
>> > have tried a few methods that I found while searching the internet to
>> > no avail. I believe it may be in part to a few issues.
>> > 1) both tables have the same layout. Columns are named the same and
>> > same type.
>> > 2) the first 5 columns are used to create the primary key.
>>
>> > I am running in a Oracle 8i environment so it appears the merge
>> > command is not available.
>>
>> > Here is the last thing I tried:
>>
>> > UPDATE (SELECT REG.*, REGTEMP.* FROM REG, REGTEMP
>> > WHERE REG.COL1 = REGTEMP.COL1,
>> > AND REG.COL2 = REGTEMP.COL2,
>> > AND REG.COL3 = REGTEMP.COL3,
>> > AND REG.COL4 = REGTEMP.COL4,
>> > AND REG.COL5 = REGTEMP.COL5)
>> > SET COL6 = COL6_1,
>> > COL7 = COL7_1,
>> > COL8 = COL8_1,
>> > COL9 = COL9_1;
>>
>> > INSERT INTO REG (SELECT * FROM REGTEMP WHERE NOT IN (SELECT REGTEMP.*
>> > FROM REG, REGTEMP
>> > WHERE REG.COL1 = REGTEMP.COL1,
>> > AND REG.COL2 = REGTEMP.COL2,
>> > AND REG.COL3 = REGTEMP.COL3,
>> > AND REG.COL4 = REGTEMP.COL4,
>> > AND REG.COL5 = REGTEMP.COL5);
>>
>> > The update give me this error:
>> > ORA-01779 cannot modify a column which maps to a non key-preserved
>> > table.
>>
>> > Please note. I do not mind ditching the code used above. I believe
>> > there are easier ways of doing this. Also I would like to know the
>> > correct method to approach this task. I am open to any suggestions.
>>
>> > Thank you for your time,
>>
>> > Eric
>>
>> You are using 9i syntax in the update statement and the insert
>> statement.
>>
>> The update should look like
>> update reg r
>> set (r.col_6, r.col_7, r.col_8, r.col_9) =
>> (select rt.col_6, rt.col_7, rt.col_8, rt.col_9
>> from regtemp rt
>> )
>> where exists
>> (select 'x'
>> from regtemp rt
>> where rt.col1 = r.col1
>> and rt.col2 = r.col2
>> and rt.col3 = r.col3
>> and rt.col4 = r.col4
>> and rt.col5 = r.col5
>> )
>>
>> the insert statement should have read
>>
>> INSERT INTO REG r
>> SELECT *
>> FROM REGTEMP
>> WHERE not exists
>> (select 'x'
>> FROM REGTEMP rt
>> WHERE Rt.COL1 = r.COL1,
>> AND rt.COL2 = r.COL2,
>> AND Rt.COL3 = r.COL3,
>> AND rt.COL4 = r.COL4,
>> AND rt.COL5 = r.COL5);
>>
>> Obviously this proper SQL in any version, so your problem seems to be
>> you have been exposed to much to the Microsux dialect of SQL.
>>
>> 8i has been desupported a long time ago, 9i will follow soon. I would
>> recommend looking for a different employer.
>>
>> --
>> Sybrand Bakker
>> Senior Oracle DBA- Hide quoted text -
>>
>> - Show quoted text -
>
>I was able to get the update working without a problem.
>
>The insert does not seem to work for me.
>
>I researched the errors. I then added the "REG r", removed the first
>"r" and removed the commas after the r.COL# . Now the query runs but
>does not process any data. I verified that the subquery finds the
>correct rows and that there are many rows outside of the subquery.
>
>INSERT INTO REG r
> SELECT *
> FROM REGTEMP
> WHERE not exists
> (select 'x'
> FROM REGTEMP rt, REG r
> WHERE Rt.COL1 = r.COL1
> AND rt.COL2 = r.COL2
> AND Rt.COL3 = r.COL3
> AND rt.COL4 = r.COL4
> AND rt.COL5 = r.COL5);
>
>Thank you so much for your help,
>
>Eric

Ok, I will vow stop doing anyone's homework, especially using copy and paste

The query should have read

INSERT INTO REG r
SELECT *
FROM REGTEMP rt
WHERE not exists
(select 'x'
 FROM REG r1

      WHERE Rt.COL1 = r1.COL1,
           AND rt.COL2 = r1.COL2,
           AND Rt.COL3 = r1.COL3,
           AND rt.COL4 = r1.COL4,
           AND rt.COL5 = r1.COL5);

Please try to explain why your solution doesn't provide any rows. Your answer should use the words 'INNER JOIN'.

-- 
Sybrand Bakker
Senior Oracle DBA