Another newbie question about UPDATE

Hi !

Another question from this stupid danish oracle-newbie :-)

I have 2 tables, STOCKTABLE and STOCKTABLE_EXT

I want to update 2 fields in STOCKTABLE, with the values of 2 fields in STOCKTABLE_EXT.

STOCKTABLE has the following fields:
Itemnumber ItemName Itemgroup
---------- ------------ ---------

1          Hammer       10
2          Screwdriver  10
3          saw          10


STOCKTABLE_EXT has the following fields:
Itemnumber ItemName     ItemGroup


---------- ------------ ---------

1          Book         20
2          Knife        20
4          Fork         30

I want to update STOCKTABLE, so the result will be:
Itemnumber ItemName     Itemgroup


---------- ------------ ---------

1          Book         20
2          Knife        20
3          saw          10

I have tried:

UPDATE STOCKTABLE st

   SET (st.ItemName, st.ItemGroup) =

          (SELECT ste.ItemName, ste.ItemGroup
             FROM STOCKTABLE_EXT ste
            WHERE st.itemnumber = ste.itemnumber)

But i get an ORA-01407 "Cannot update STOCKTABLE.ItemName to NULL".

So obviously there is something wrong with my statement. What am i doing wrong ?

Of course i could try:

UPDATE STOCKTABLE st

   SET st.ItemName = (SELECT ste.ItemName

                      FROM STOCKTABLE_EXT ste
                      WHERE st.itemnumber = ste.itemnumber),
   SET st.ItemGroup = (SELECT ste.ItemGroup
                      FROM STOCKTABLE_EXT ste
                      WHERE st.itemnumber = ste.itemnumber),
where exists (SELECT ste.ItemNumber
              FROM STOCKTABLE_EXT ste
              WHERE st.itemnumber = ste.itemnumber)

But this don't seem right.

Anyone has better idea ?

Regards,
Ole Received on Tue May 24 2005 - 09:08:59 CDT