Table Update 2

[Andy7777]

Hi, I'm an Oracle novice so any help is appreciated. I have 2 tables with fields shown below:

MASTER TEMP
PartNumber PartNumber
MaxStock MaxStock
other fields calculation fields

TEMP contains a subset of parts from MASTER and I want to update the MaxStock in MASTER with the TEMP MaxStock (PartNumber is Primary Key for both and no duplicates). I've tried:

UPDATE MASTER SET MASTER.MAXSTOCK = TEMP.MAXSTOCK FROM TEMP WHERE MASTER.PARTNUMBER = TEMP.PARTNUMBER (error ORA-00933 SQL command not properly ended)

UPDATE (SELECT DISTINCT MASTER.PARTNUMBER, TEMP.PARTNUMBER, MASTER.MAXSTOCK, TEMP.MAXSTOCK FROM MASTER, TEMP WHERE MASTER.PARTNUMBER = TEMP.PARTNUMBER) SET MASTER.MAXSTOCK = TEMP.MAXSTOCK (error ORA-00904 invalid column name)

UPDATE MASTER SET MASTER.MAXSTOCK = (SELECT TEMP.MAXSTOCK FROM TEMP WHERE MASTER.PARTNUMBER = TEMP.PARTNUMBER) (error ORA-01407 cannot update MaxStock field to NULL)

Bit puzzled. Any ideas.
Thanks, Andy

 

[Turkbear]

Hi,
( Time to read some Oracle docs on SqlPlus):

Try:

Update Master set MaxStock = (select maxstock from temp where Master.partnumber = temp.partnumber and temp.partnumber is not null);

You must have some partnumbers in TEMP with NULL MaxStock.

 

[Andy7777]

Hi Turkbear,

Thanks, but I still get error ORA-01407 cannot update MaxStock to Null (I know there are no Null values in either table).

 

[LKBrwnDBA]

You need only to update the ones that actually EXIST in temp!!!

Update Master 
set MaxStock = (
    select maxstock from temp 
     where Master.partnumber = temp.partnumber
       and temp.partnumber is not null)
Where EXISTS (
    select 1 from temp 
     where Master.partnumber = temp.partnumber
       and temp.partnumber is not null);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Andy7777]

Thanks, that works fine.

 

[Turkbear]

Hi,
Great catch, LKBrwnDBA - I think it deserves a star since in reveals an important point about UPDATE statements ( and DELETES as well )
- Without a where clause on the Target table to limit which records are affected by the action, ALL the records will be affected - which can have really bad results.