Re: Another newbie question about UPDATE

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

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_108a.htm#2067717

<quote>
subquery

Specify a subquery that returns exactly one row for each row updated.

• If you specify only one column in the update_set_clause, then the subquery can return only one value.
• If you specify multiple columns in the update_set_clause, then the subquery must return as many values as you have specified columns.

You can use the flashback_clause of within the subquery to update table with past data.
See Also:

the flashback_clause of SELECT for more information on this clause

If the subquery returns no rows, then the column is assigned a null. </quote>

In your first example you didn't get any rows for itemnumber 3, there is obviously ( if not , your update would succeed and you get a result which you probably don't expect ) not null constraint on itemname and you got ORA 1407. In your second example you restrict the rows that should be updated, that way it works properly. In general there are many

   ways to write an update based on subquery, but you should always (never say always - maybe you wish update all rows? ;-) restrict the rows that should be updated in WHERE clause of UPDATE statement, subquery doesn't do that , it delivers only new values for updated rows.

Best regards

Maxim Received on Thu May 26 2005 - 08:10:40 CDT