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

Ole Hansen wrote:
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@x.washington.edu
(replace x with u to respond)

Ole Hansen schrieb:
http://download-west.oracle.com/docs...8a.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

Ole Hansen wrote:
Given that error message why do you think the problem is in your
statement? I would first look in the data. Is there a row (or many) in
STOCKTABLE_EXT that has a NULL ItemName??

Ed
Two proverbs:
1. Sometimes it doesn't pay to overlook the obvious.
2. Know your data.

Ed Prochak wrote:
LOL
would it not be that there are rows in STOCKTABLE without data in STOCKTABLE_EXT?

He should have kept his where-clause from his first try.