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

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 Received on Wed Mar 21 2007 - 11:44:34 CDT