Link to home
Start Free TrialLog in
Avatar of ftaco96
ftaco96

asked on

UPDATE statement help

Hi,
I've got this stored proc that needs to update multiple rows in a table (DETAIL) using specific criteria that's in a subquery, but I get this error, I believe on the WHERE clause at the end of the update:
PLS-00201: identifier 'smy.CYCLE_ID' must be declared

CREATE PROCEDURE SP_DETAIL
   ( PARM_MARKET IN VARCHAR2 )
IS
    dtLastRpt DATE;      
    dtPrevRpt DATE;
    iMarketID NUMBER;
BEGIN

SELECT market_id INTO iMarketID
FROM MARKET
WHERE MARKET = PARM_MARKET;

SELECT MAX(wmd.START_TIME)
INTO dtLastRpt
FROM MARKET_DAILY md
WHERE md.market_id = iMarketID
    AND md.job_id = 41
    AND md.start_time < SYSDATE;

SELECT MAX(md.START_TIME)
INTO dtPrevRpt
FROM MARKET_DAILY md
WHERE md.market_id = iMarketID
    AND md.job_id = 41
    AND md.start_time < dtLastBLNHB;

UPDATE DETAIL det
SET (rejects, modified_by, modify_date) =
(SELECT
   smy.REJECT, 'myid' as modified_by, sysdate as modify_date
FROM
   ACTUALS act
 , CYCLE cyc
 , SUMMARY smy
WHERE
     act.CYCLE_ID = cyc.CYCLE_ID
 AND smy.CYCLE_ID = cyc.CYCLE_ID
 AND smy.MONTH_YEAR = act.MONTH_YEAR
 AND cyc.MARKET_ID = iMarketID
 AND act.EVENT_ID = 32
 AND act.CYCLE_TYPE = 'M'
 AND act.actual_time < dtLastRpt
 AND act.actual_time > dtPrevRpt
)
WHERE det.CYCLE_ID = smy.cycle_id
    and det.MONTH_YEAR = smy.month_year;

END;

Anybody have any ideas?
ASKER CERTIFIED SOLUTION
Avatar of SDutta
SDutta

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of seazodiac
I think differently,  this might be a simple error:

I suspect in Summary table, there is no cycle_id column, it might be named idfferently.



try to post the output of this command:

SQL>desc summary
Definitely double-check the column as seazodiac suggested. I had a problem yesterday with exactly the same error message. Even though I was sure I had the column names right, seazodiac recommended the DESCRIBE <table> and I discovered the column name wasn't what I thought it was.
Avatar of oratim
oratim

Actually, SDutta is on the right track, and the summary table cannot be referenced in the where clause of the update statement because it is only declared in the nested select statement. His solution should work nicely for you. If the column CYCLE_ID was not in the summary table, I would think that the Error would show in the nested select statement instead.
@ftaco96,
Any updates - if my solution worked or not ? In fact what Seazodiac suggests may also be true but then it is too simple a scenario and there would be no possibility of your update statement working at all.
Avatar of ftaco96

ASKER

SDutta, I just tried it and it works like a charm. Thanks.
Avatar of ftaco96

ASKER

I was running this query again today, and I got an error:
ORA01427 - single row sub-query returns more than one row

I didn't think this was an issue with this query because the whole point was to update multiple rows. Apparently, though, the first select subquery is getting more than one row, and Oracle is choking on it.

Yes, you can only get one row with a select statement inside a procedure unless you make a cursor of it.

If you have multiple rows with the same condition and you are sure that the rows will return the same values, you can add to the existing condition " AND ROWNUM = 1" to get only one row.