Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Addendum
Both this and the where exists work great. Excellent suggestions!
why did
update B set offset = A.offset
where A.primarykey = B.primarykey.
claim to have updated every row in B without actually doing it?
David Pattinson wrote:
> Doug,
>
> what about:
>
> update B
> set B.offset = (select DECODE(A.offset,NULL,B.offset,A.offset) from A where a.pk
> = B.pk) ;
>
> (a sneaky trick... which seems to work)
>
> HTH, David.
>
> > These tables are actually not exactly the same, they have 388
> > primary key columns in common (those are the ones I care about)
> > the count on table b is 527 and the other is 458. 388 overlap.
> > I tried something a little different but no luck - I tried
> > update B set offset = A.offset
> > where A.primarykey = B.primarykey.
> > It returns 527 rows updated, wheras there are only 388 in common. There
> > are no duplicates in the key column. Also, not all the updates made it. I am
> >
> > confused.
> >
> > Doug Cowles wrote:
> >
> > > I need to do a large update and can't quite figure it out. Essentially,
> > >
> > > I have two tables of the same name and fieldtypes in two different
> > > schemas.
> > > The only difference is that on one table, a field called "offset" has
> > > values in
> > > it and on the other table (table B), it doesn't. The idea is to get
> > > table b to
> > > look exactly like table a.
> > >
> > > I tried
> > >
> > > Update B set offset =
> > > (select offset from A where primarykey = B.primarykey)
> > >
> > > and got
> > > cannot update mandatory (NOT NULL) column to NULL.
> > > The offset is not null on both tables, so there are no null values. My
> > > correlation
> > > logic must be askew.
> > >
> > > Would appreciate any advice,
> > >
> > > - Dc.
Received on Sat May 22 1999 - 18:32:14 CDT