Need help selecting from cursor as if it were a worktable

Question

I have a stored procedure I am trying write and I wanted to use a cursor as a worktable so I could use the results to update a table. It is throwing a few errors. Would anyone be able to help me resolve these?

create or replace PROCEDURE SP_POPULATE_STUDENT_STOPTEMPID

AS

CURSOR c_workTable IS
SELECT  
    stdnt.STUDENT_ID, 
    stdnt.route_code, 
    stdnt.DISPATCH_TYPE, 
    stdnt.RUN_CODE, 
    stdnt.STOP_ADDRESS, 
    stdnt.STOP_TIME, 
    ST.STOP_TEMPLATE_ID 
FROM  
    STOP_TEMPLATE ST 
    JOIN TASK_TEMPLATE TT 
        ON ST.TASK_TEMPLATE_ID = TT.TASK_TEMPLATE_ID 
    JOIN RUN_TEMPLATE RT 
        ON ST.RUN_TEMPLATE_ID = RT.RUN_TEMPLATE_ID,  
    STUDENT stdnt
WHERE 
    TT.TASK_NAME = stdnt.route_code 
    AND TT.DISPATCH_TYPE = stdnt.DISPATCH_TYPE 
    AND RT.RUN_CODE = stdnt.RUN_CODE 
    AND ST.STOP_DESCRIPTION = stdnt.STOP_ADDRESS 
    AND CAST(ST.EXPECTED_ARRIVAL_TIME AS TIME(7)) = stdnt.STOP_TIME ;

BEGIN

UPDATE STUDENT stdnt  
SET stdnt.STOP_TEMPLATE_ID = c_workTable .STOP_TEMPLATE_ID 
FROM  
    c_workTable 
WHERE  
    stdnt.STUDENT_ID = c_workTable .STUDENT_ID 
    AND stdnt.route_code = c_workTable .route_code 
    AND stdnt.DISPATCH_TYPE = c_workTable .DISPATCH_TYPE 
    AND stdnt.RUN_CODE = c_workTable .RUN_CODE 
    AND stdnt.STOP_ADDRESS = c_workTable .STOP_ADDRESS     
    AND stdnt.STOP_TIME = c_workTable .STOP_TIME 


END SP_POPULATE_STUDENT_STOPTEMPID;

ETA: changed some var names in query

I have written this based on a sql server stored procedure but I am getting a few errors in the update statement.

On UPDATE STUDENT stdnt

Error(30,5): PL/SQL: SQL Statement ignored

Then on the from clause I get this error

Error(32,5): PL/SQL: ORA-00933: SQL command not properly ended

Finally on the END clause I get

Error(42,35): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with
<< continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall merge pipe purge

--new code
UPDATE(
SELECT stdnt.STOP_TEMPLATE_ID as old_val,
ST.STOP_TEMPLATE_ID as new_val
FROM STUDENT stdnt
    JOIN STOP_TEMPLATE ST 
    JOIN TASK_TEMPLATE TT 
        ON ST.TASK_TEMPLATE_ID = TT.TASK_TEMPLATE_ID 
    JOIN RUN_TEMPLATE RT 
        ON ST.RUN_TEMPLATE_ID = RT.RUN_TEMPLATE_ID
    JOIN STUDENT stdnt
        ON (TT.TASK_NAME = stdnt.route_code 
            AND TT.DISPATCH_TYPE = stdnt.DISPATCH_TYPE 
            AND RT.RUN_CODE = stdnt.RUN_CODE 
            AND ST.STOP_DESCRIPTION = stdnt.STOP_ADDRESS 
            AND CAST(ST.EXPECTED_ARRIVAL_TIME AS TIMESTAMP(7)) = 
stdnt.STOP_TIME)
) t
SET t.old_val = t.new_val;

Is there a reason why you need to do it this way? It would be quicker, easier and more maintainable to do the update in a single SQL statement (probably a MERGE statement). — Boneist, Oct 01 '19 at 15:13
There is no real reason other than my inexperience with sql in general... — Kevin Lord of Spaghetti Code, Oct 01 '19 at 17:32
It was included in my qorkspace, I just suck at copying. I adjusted the original post. — Kevin Lord of Spaghetti Code, Oct 02 '19 at 14:28

score 1 · Answer 1 · answered Oct 01 '19 at 18:13

1

Another and better way is to use the MERGE statement in ORACLE to update the table from 2 or more tables -

MERGE INTO STUDENT stdnt
USING (SELECT  SRM.STUDENT_ID, 
               SRM.route_code, 
               SRM.DISPATCH_TYPE, 
               SRM.RUN_CODE, 
               SRM.STOP_ADDRESS, 
               SRM.STOP_TIME, 
               ST.STOP_TEMPLATE_ID 
       FROM STOP_TEMPLATE ST 
       JOIN TASK_TEMPLATE TT ON ST.TASK_TEMPLATE_ID = TT.TASK_TEMPLATE_ID 
       JOIN RUN_TEMPLATE RT ON ST.RUN_TEMPLATE_ID = RT.RUN_TEMPLATE_ID,  
       STUDENT_ROUTE_MAPPING SRM 
       WHERE TT.TASK_NAME = SRM.route_code 
       AND TT.DISPATCH_TYPE = SRM.DISPATCH_TYPE 
       AND RT.RUN_CODE = SRM.RUN_CODE 
       AND ST.STOP_DESCRIPTION = SRM.STOP_ADDRESS 
       AND CAST(ST.EXPECTED_ARRIVAL_TIME AS TIME(7)) = SRM.STOP_TIME) I
ON (stdnt.STUDENT_ID = I.STUDENT_ID 
    AND stdnt.route_code = I.route_code 
    AND stdnt.DISPATCH_TYPE = I.DISPATCH_TYPE 
    AND stdnt.RUN_CODE = I.RUN_CODE 
    AND stdnt.STOP_ADDRESS = I.STOP_ADDRESS     
    AND stdnt.STOP_TIME = I.STOP_TIME)
WHEN MATCHED THEN UPDATE
                  SET stdnt.STOP_TEMPLATE_ID = I.STOP_TEMPLATE_ID

answered Oct 01 '19 at 18:13

Ankit Bajpai

13,128
4
25
40

Why do you consider `MERGE` a better option to update the table from 2 or more tables? I'd say that using `UPDATE` is always better for sole updating purposes. `MERGE` surely provides the possibility to both update and insert in a table at the same time (which is not needed here), but I don't see how it can be a better solution for only updating than `UPDATE` itself? – Goran Kutlaca Oct 02 '19 at 07:40
1

@GoranKutlaca a `MERGE` statement may insert and/or update. It has the advantage that it can be clearer than an `UPDATE` statement for update-only and can pick up better execution plans. Also, your subquery/view needs to be key-preserved to use in an `UPDATE` statement, but not in a `MERGE` statement (the source data must, however, be unique for the `ON` join condition(s)). – Boneist Oct 02 '19 at 15:03
I am having trouble with the update as it says my base table isn t key preserved. What does that mean? How do I key preserve the tables? I am not very experienced with sql so forgive me if this is a dumb question. – Kevin Lord of Spaghetti Code Oct 02 '19 at 18:31
@KevinLordofSpaghettiCode, You should read https://asktom.oracle.com/pls/apex/f?p=100:11:13146487562680::::P11_QUESTION_ID:548422757486 for understanding Key-preserved table. The join your are using as inner query is having some repeating rows.You need to correct it since we have not seen your data yet. – Ankit Bajpai Oct 02 '19 at 19:00

Goran Kutlaca · Answer 2 · 2019-10-02T14:58:22.610

0

You can achieve it by using one UPDATE statement that utilizes JOIN:

UPDATE(
    SELECT  
        stdnt.STOP_TEMPLATE_ID as old_val 
      , ST.STOP_TEMPLATE_ID as new_val
    FROM  
        STOP_TEMPLATE ST 
        JOIN TASK_TEMPLATE TT 
            ON ST.TASK_TEMPLATE_ID = TT.TASK_TEMPLATE_ID 
        JOIN RUN_TEMPLATE RT 
            ON ST.RUN_TEMPLATE_ID = RT.RUN_TEMPLATE_ID
        JOIN STUDENT stdnt
            ON (    TT.TASK_NAME = stdnt.route_code 
                AND TT.DISPATCH_TYPE = stdnt.DISPATCH_TYPE 
                AND RT.RUN_CODE = stdnt.RUN_CODE 
                AND ST.STOP_DESCRIPTION = stdnt.STOP_ADDRESS 
                AND CAST(ST.EXPECTED_ARRIVAL_TIME AS TIME(7)) = stdnt.STOP_TIME)
) t
SET t.old_val = t.new_val;

Link to another question and answers that might help: Update statement with inner join on Oracle.

Alternative but similar solution would be getting a value for update from a subquery:

UPDATE STUDENT stdnt
   SET stdnt.STOP_TEMPLATE_ID = (
    SELECT  
        ST.STOP_TEMPLATE_ID
    FROM  
        STOP_TEMPLATE ST 
        JOIN TASK_TEMPLATE TT 
            ON (ST.TASK_TEMPLATE_ID = TT.TASK_TEMPLATE_ID 
                AND TT.TASK_NAME = stdnt.route_code 
                AND TT.DISPATCH_TYPE = stdnt.DISPATCH_TYPE)
        JOIN RUN_TEMPLATE RT 
            ON (ST.RUN_TEMPLATE_ID = RT.RUN_TEMPLATE_ID
                AND RT.RUN_CODE = stdnt.RUN_CODE)
    WHERE ST.STOP_DESCRIPTION = stdnt.STOP_ADDRESS 
      AND CAST(ST.EXPECTED_ARRIVAL_TIME AS TIME(7)) = stdnt.STOP_TIME
);

Link to an answer on a related problem with similar solution: ORA-00933: SQL command not properly ended error on update statement

edited Oct 02 '19 at 14:58

answered Oct 01 '19 at 14:52

Goran Kutlaca

2,014
1
12
18

Im getting an error using your second suggestion for a missing keyword between the end parenthesis before the alias for the subquery is set. After reading through the link you posted I am still unable to resolve this. Would you have an idea of what might be wrong? – Kevin Lord of Spaghetti Code Oct 01 '19 at 18:31
Ignore my previouscomment. I just realized i copied in an older version that I hadnt finished converting from its old schema. Ive edited my question to be more accurate. My bad – Kevin Lord of Spaghetti Code Oct 01 '19 at 18:39
@KevinLordofSpaghettiCode I edited my answer now. Previous error technically was for omitting join clause in first part of the join. In practice it was for mistakenly keeping `STUDENT` table written although it gets joined afterwards, at the last step in `JOIN`. My edit now corrects the error and also doesn't include `STUDENT_ROUTE_MAPPING` table after you've removed it from your question. – Goran Kutlaca Oct 02 '19 at 07:35
Thank you for the clarification. I really apprecaite the help. Im sorry to be a pain but I am still getting the error "SQL command not properly ended" in between the parenthesis right before the alias t is assigned to the subquery. I have tried a few different things but have been unable to resolve this. My experience is with mysql so I am not entirely sure what it could be. Do I need to add AS or some other keyword to finish it off? – Kevin Lord of Spaghetti Code Oct 02 '19 at 14:22
@KevinLordofSpaghettiCode I edited my answer by adding alternative solution – Goran Kutlaca Oct 02 '19 at 14:59

Need help selecting from cursor as if it were a worktable

2 Answers2