Trouble with SQL join on update statement

[David Hogston]

Having a bad time figuring out what is wrong with my update statement. Been a couple of years since I wrote any scripts. Can anybody tell me what I am doing wrong?


Select Statement work fine.

SELECT DESCRIPTION
from maximo.inventor y join maximo.item
on inventory.itemn um = item.itemnum
WHERE MAXIMO.ITEM.ITE MNUM=MAXIMO.INV ENTORY.ITEMNUM AND
(MAXIMO.INVENTO RY.MANUFACTURER ='UNKNOWN' OR MAXIMO.INVENTOR Y.MANUFACTURER IS NULL) AND
INSTR(UPPER(MAX IMO.ITEM.DESCRI PTION),'OMRON') > 0;


Yet there is something wrong in the update statement that I cannot see although I know it should be easy to find. HELP!!

update maximo.inventor y
set maximo.inventor y.manufacturer= 'OMRON'
from maximo.inventor y join maximo.item
on maximo.inventor y.itemnum = maximo.item.ite mnum
WHERE MAXIMO.ITEM.ITE MNUM=MAXIMO.INV ENTORY.ITEMNUM AND
(MAXIMO.INVENTO RY.MANUFACTURER ='UNKNOWN' OR MAXIMO.INVENTOR Y.MANUFACTURER IS NULL) AND
INSTR(UPPER(MAX IMO.ITEM.DESCRI PTION),'OMRON') > 0;

Error starting at line 8 in command:
update inventory
set manufacturer='O MRON'
from maximo.inventor y left outer join maximo.item
on maximo.inventor y.itemnum=maxim o.item.itemnum
WHERE MAXIMO.ITEM.ITE MNUM=MAXIMO.INV ENTORY.ITEMNUM AND
(MAXIMO.INVENTO RY.MANUFACTURER ='UNKNOWN' OR MAXIMO.INVENTOR Y.MANUFACTURER IS NULL) AND
INSTR(UPPER(MAX IMO.ITEM.DESCRI PTION),'OMRON') > 0
Error at Command Line:9 Column:24
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:

Line 9 Column 24 is at the end of this statement
set manufacturer='O MRON'

[amitpatel66]

your update statement should be something like this:

[code=oracle]
update inventory inv
set inv.manufacture r='OMRON'
WHERE inv.itemnum=(SE LECT itm.itemnum
FROM item itm WHERE itm.ITEMNUM=inv .ITEMNUM
AND INSTR(UPPER(itm .description),' OMRON') > 0
AND (INV.MANUFACTUR ER='UNKNOWN' OR INV.MANUFACTURE R IS NULL)
AND EXISTS (SELECT 1 FROM item WHERE item_num = inv_itemnum
AND INSTR(UPPER(itm .description),' OMRON') > 0)
/
[/code]