SQLPLUS Table Update Where Mismatch

[zoroaster]

Hi

I've got a basic understanding of SQL, but am at a deadend here. I'm trying to directly update one table with the values from another where there is a mismatch (the tables are supposed to be in sync sharing the same keys) using SQLPLUS - I'm trying this in the Test area rather than on live data until I can get it right.

The following SQL retrieves the rows where there is a mismatch on the fields post.negb_c and zpost.neg:

select p.post_no,
       p.negb_c,
       z.neg
  from zpost z,
       post p
 where z.post_no=p.post_no
   and z.org_no=p.org_no
   and z.neg<>p.negb_c;

I want to update the mismatched values in the NEGB_C field on the post table with the values of NEG from the zpost table; however, I simply can't work out the exact syntax. My last effort was:

update post 
set negb_c = (select neg from zpost 
              where post.org_no = zpost.org_no
                and post.post_No = zpost_no
                and zpost.post_no is not null)
Where EXISTS (select 1 from zpost 
           where post.post_no = zpost.post_no
             and post.org_no = zpost.org_no
             and post.negb_c <> zpost.neg
             and zpost.post_no is not null);

which returns
ERROR at line 9:
ORA-00904: invalid column name

I've consulted the Oracle SQL reference, and various tutorials but can't seem to nail it. Anyone any ideas where I'm going wrong here?

Many thanks

Laters, Z

"42??? We're going to get lynched!

 

[hoinz]

ORA-00904: invalid column name

Usually this means what it says.
Most probably you got a column name wrong. Check them.
(You did not post the names of all your columns, so we cannot do it for you.)

but here's a guess:
and post.post_No = zpost_no

Are you sure there is column zpost_no ?

hope this helps

 

[zoroaster]

Oops! A very stupid and simple mistake, I shall cringe with embarrassment and scuttle off to a darkened corner and pray that this thread gets deleted!

Thankyou for pointing out the obvious, I obviously needed that!!!! Sorry for wasting your time...


Laters, Z

"42??? We're going to get lynched!