Re: Oracle Update SQL Help

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/04/19
Message-ID: <8dkdf9$gi7$1@nnrp1.deja.com>#1/1

In article <8dj37n$7rrch$1_at_fu-berlin.de>,   "Lanky" <mdang_at_NOSPAMmy-deja.com> wrote:
> Didn't work got a Oracle error 1773 can't update a rs
>
> --
> Thanks,
>

It works -- you must have typed in something wrong. ORA-1773 is:

$ oerr ora 1773
01773, 00000, "may not specify column datatypes in this CREATE TABLE"

so I don't see how that could be coming from an UPDATE. Here is an example tested in 7.3.4 -> 8.1.6 that shows this working:

scott_at_ORA734.WORLD> create table tableA ( id number primary key,

  2                        col1  varchar2(10),
  3                        col2  varchar2(10),
  4                        col3  varchar2(10) )

  5 /

Table created.

scott_at_ORA734.WORLD> create table tableB ( id number primary key,

  2                        col1  varchar2(10),
  3                        col2  varchar2(10),
  4                        col3  varchar2(10) )

  5 /

Table created.

scott_at_ORA734.WORLD>
scott_at_ORA734.WORLD> begin

  2      for i in 1 .. 10 loop
  3          insert into tableA values ( i, 'col1-' || i, 'col2-' ||
i, 'col3-' || i );
  4          insert into tableB values ( i, null, null, null );
  5      end loop;

  6 end;
  7 /

PL/SQL procedure successfully completed.

scott_at_ORA734.WORLD>
scott_at_ORA734.WORLD> select * from tableb   2 /

        ID COL1 COL2 COL3
---------- ---------- ---------- ----------

         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

scott_at_ORA734.WORLD> update
  2 ( select tableb.col1 b_col1, tableb.col2 b_col2, tableb.col3 b_col3,

  3           tablea.col1 a_col1, tablea.col2 a_col2, tablea.col3 a_col3
  4     from tableA, tableB
  5     where tableA.id = tableB.id )
  6  set b_col1 = a_col1,
  7     b_col2 = a_col2,
  8     b_col3 = a_col3

  9 /

10 rows updated.

scott_at_ORA734.WORLD> select * from tableb   2 /

        ID COL1 COL2 COL3

---------- ---------- ---------- ----------
         1 col1-1     col2-1     col3-1
         2 col1-2     col2-2     col3-2
         3 col1-3     col2-3     col3-3
         4 col1-4     col2-4     col3-4
         5 col1-5     col2-5     col3-5
         6 col1-6     col2-6     col3-6
         7 col1-7     col2-7     col3-7
         8 col1-8     col2-8     col3-8
         9 col1-9     col2-9     col3-9
        10 col1-10    col2-10    col3-10

10 rows selected.

scott_at_ORA734.WORLD>

Can you post an example from sqlplus showing this getting an ORA-1773?

> Lanky
> "Thomas J. Kyte" <tkyte_at_us.oracle.com> wrote in message
> news:8divd6$uk4$1_at_nnrp1.deja.com...
> > In article <8dis0m$7p9e5$1_at_fu-berlin.de>,
> > "Lanky" <mdang_at_NOSPAMmy-deja.com> wrote:
> > > Hi All,
> > >
> > > Here's the scenario.
> > >
> > > 2 Tables Exactly the same data structure and data except for 3
 fields
 in
> > > table A are null.
> > >
> > > 1 primary key in both tables. The data is all the same in Table
 A & B
> > > except for 3 fields in Table A.
> > >
> > > Data Structure for both TBL A & B (example 1 row of data given
 below)
> > > TBL A TBL B
> > > PK - ID 1 1
> > > Col1 JKL JKL
> > > Col2 NULL ABC
> > > Col3 NULL ABC
> > > Col4 NULL ABC
> > > Col5 ABC ABC
> > >
> > > The data is the same just need to get the 3 fields in Table B into
 TAble A.
> > >
> > > This is what I got but didn't work.
> > >
> > > Update Table B
> > > set TableB.col1 = TableA.col1,
> > > TableB.col2 = TableA.col2,
> > > TableB.col3 = TableA.col3
> > > from table A
> > > where TableA.id = TableB.id
> > >
> >
> > you can update a join this way:
> >
> > update
> > ( select tableb.col1 b_col1, tableb.col2 b_col2, tableb.col3 b_col3,
> > tablea.col1 a_col1, tablea.col2 a_col2, tablea.col3 a_col3
> > from tableA, tableB
> > where tableA.id = tableB.id )
> > set b_col1 = a_col1,
> > b_col2 = a_col2,
> > b_col3 = a_col3
> > /
> >
> > A correlated subquery would be another way:
> >
> > update tableB
> > set (col1, col2, col3 ) = ( select col1, col2, col3
> > from tableA
> > where tableA.id = tableB.id )
> > where exists ( select col1, col2, col3
> > from tableA
> > where tableA.id = tableB.id )
> > /
> >
> >
> >
> > > --

.....

>

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Wed Apr 19 2000 - 00:00:00 CDT