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

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