Syntax error ORA-00904 trying to do Update

[therayster]

Trying to update a table with values from a view. Here's my statement:
SQL> update itemloc
2 set fifo_dt=(select lot_dt from fifochk)
3 where itemloc.location=fifochk.location

Here's the error I get:
where itemloc.location=fifochk.location
*
ERROR at line 3:
ORA-00904: invalid column name

FIFOCHK is a view laid out thusly:
SQL> desc fifochk
Name Null? Type
------------------------------- -------- ----
LOCATION VARCHAR2(12)
LOT_DT DATE

ITEMLOC is a table that looks like this:
SQL> desc itemloc
Name Null? Type
------------------------------- -------- ----
ITM_NUM NOT NULL VARCHAR2(20)
LOCATION NOT NULL VARCHAR2(12)
QTY_ON_HAND NUMBER(9)
QTY_ALLOC NUMBER(9)
QTY_TO_STORE NUMBER(9)
DED_TYPE CHAR(1)
FIFO_DT DATE
MAX_CAP NUMBER(9)
LAST_CC_DT_TM DATE
CC_STAT_TYPE CHAR(1)
LIC_CTRL_FLG CHAR(1)
QTY_AVAIL_DAM NUMBER(9)
ITM_MIX_FLG CHAR(1)
LOT_CTRL_FLG CHAR(1)
QTY_MVIN NUMBER(9)
QTY_MVOT NUMBER(9)
LOC_CLASS NUMBER(4)
WHS_ZONE NUMBER(4)
PICK_SEQ_NUM NUMBER(9)
QTY_ON_HOLD NUMBER(9)
HELD_QTY_ALLOC NUMBER(9)
HOLD_EMPTY_FLG CHAR(1)
EP_HOLD_FLG CHAR(1)
SCH_HELD_QTY NUMBER(9)
ITM_CONF_SEQ NUMBER(4)
CONF_DED_FLG CHAR(1)

ITEMLOC should have about 700 rows in it at any given time, the FIFOCHK view will have about 500. FIFOCHK.LOCATION should be a unique value within the view but each may have multiple matches against ITEMLOC.LOCATION. Can somebody tell me how to get this working?

Ray

 

[lweng]

Try
2 set fifo_dt=(select lot_dt from fifochk
3 where itemloc.location=fifochk.location)

 

[therayster]

Not quite...
SQL> update itemloc
2 set fifo_dt=(select lot_dt from fifochk
3 where itemloc.location=fifochk.location)
4 /
where itemloc.location=fifochk.location)
*
ERROR at line 3:
ORA-01843: not a valid month

I'm thinking using a loop would be easier but I'm more comfortable doing that sort of thing from VB rather than PL/SQL, and I wanted to try and get this working just so I'd know for future reference.

 

[raj28]

Hi theryaster,
Just try this -
Update itemloc a set fifo_dt=(select lot_dt from fifochk b where a.location=b.location)
where location in ( select location from fifochk )
Hope this helps ..