Re: Another newbie question about UPDATE

Ole Hansen wrote:
> 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

Please tell me that, and I'm looking at the calendar and know that I am giving my students their final tonight, this isn't school work.

Simply put ... your update syntax is wrong. Look up the correct syntax which you can find in your book, in your notes, at tahiti.oracle.com, at psoug.org and numerous other places.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)