Update SQL syntax

[sxschech]

When I run this, the error message is command not properly ended. I haven't done update queries in Oracle, have done them in Access using the graphical interface, so probably haven't written correctly. Trying to update the decision and date by only selecting one record from the decision table which has the decision date less than the cutoff date specified by a date in the cutoff table.

UPDATE APP_STAT a 
SET a.DECISION = ad.DECISION, a.DECISION_DATE = ad.DECISION_DATE
LEFT JOIN 
            (SELECT *
             FROM (SELECT decision.*,
                     ROW_NUMBER () OVER (PARTITION BY person_uid ORDER BY decision_date DESC, decision_number DESC)
                                                                          dt
                   FROM mst_admissions_decision decision, sxschech.CUTOFF 
                   WHERE decision_date IS NOT NULL AND decision_date <= cutoff_dt
                   --and academic_period in ('200830','200840')
                  )
             WHERE dt = 1 
            ) ad 
        ON  a.APPLICATION_NUMBER = ad.APPLICATION_NUMBER 
        AND a.ACADEMIC_PERIOD = ad.ACADEMIC_PERIOD 
        --AND a.Academic_period in ('200830','200840')
        AND a.person_uid = ad.PERSON_UID;

 

[SantaMufasa]

Sxschech,

It appears to me that you want to UPDATE two columns in the APP_STAT table with results that you gather from joining "mst_admissions_decision decision, sxschech.CUTOFF". (Am I correct in my presumptions?)

First, I am not aware that Oracle's UPDATE statement allows for LEFT JOIN syntax...only for standard WHERE conditions. To help you formulate the UPDATE statement that you want, I suggest that you first formulate a SELECT statement (that uses your LEFT JOIN syntax and logic), without attempting any UPDATE-ing, that generates the "future" UPDATE values that you want "...WHERE <some_condition exists>" for just the rows you want UPDATEd. Once you do that, then I can guarantee that we can assist you in producing a working UPDATE statement that does what you want.

So, in summary, what I'm asking for is a working SELECT (without UPDATE) that produces the values that you eventually want to use in your UPDATE statement.

Looking forward to your next post,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[sxschech]

Hi Santa,
Was out on Friday. Here is the SQL statement without the UPDATE.

[tt]
select a.person_uid, ad.DECISION, ad.DECISION_DATE
FROM app_stat a LEFT JOIN
(SELECT *
FROM (SELECT decision.*,
ROW_NUMBER () OVER (PARTITION BY person_uid ORDER BY decision_date DESC, decision_number DESC)
dt
FROM mst_admissions_decision decision, sxschech.CUTOFF
WHERE decision_date IS NOT NULL AND decision_date <= cutoff_dt
)
WHERE dt = 1
) ad
ON a.APPLICATION_NUMBER = ad.APPLICATION_NUMBER
AND a.ACADEMIC_PERIOD = ad.ACADEMIC_PERIOD
AND a.person_uid = ad.PERSON_UID;
[/tt]

The mixture of Oracle and other syntax is due to having trouble figuring out how to write the joins (This query was part of a bigger query). I copied the SQL and pasted into Access and then used the graphical SQL editor. Then copied it back to Oracle.

 

[SantaMufasa]

Sxscheck,

As you have probably discovered, performing Oracle UPDATEs from Oracle queries (SELECTs) can be rather complex. Here is code that should do what you want. (Since I do not have tables with which to test, I must rely upon your testing.):

update app_stat x
   set (x.DECISION,x.DECISION_DATE) = 
       (select ad.DECISION, ad.DECISION_DATE
          FROM app_stat a LEFT JOIN
               (SELECT *
                  FROM (SELECT decision.*,
                               ROW_NUMBER ()
                                   OVER (PARTITION BY person_uid
                                             ORDER BY decision_date DESC
                                                  ,decision_number DESC) dt
                          FROM mst_admissions_decision decision
                              ,sxschech.CUTOFF
                         WHERE decision_date IS NOT NULL
                           AND decision_date <= cutoff_dt
                       )
                 WHERE dt = 1
               ) ad
               ON  a.APPLICATION_NUMBER = ad.APPLICATION_NUMBER
               AND a.ACADEMIC_PERIOD = ad.ACADEMIC_PERIOD
               AND a.person_uid = ad.PERSON_UID
               AND x.person_uid = ad.person_uid
       )
 where exists
       (select 'any string'
          FROM app_stat a LEFT JOIN
               (SELECT *
                  FROM (SELECT decision.*,
                               ROW_NUMBER ()
                                   OVER (PARTITION BY person_uid
                                             ORDER BY decision_date DESC
                                                  ,decision_number DESC) dt
                          FROM mst_admissions_decision decision
                              ,sxschech.CUTOFF
                         WHERE decision_date IS NOT NULL
                           AND decision_date <= cutoff_dt
                       )
                 WHERE dt = 1
               ) ad
               ON  a.APPLICATION_NUMBER = ad.APPLICATION_NUMBER
               AND a.ACADEMIC_PERIOD = ad.ACADEMIC_PERIOD
               AND a.person_uid = ad.PERSON_UID
               AND x.person_uid = ad.person_uid
       )
/

The WHERE clause for the UPDATE duplicates the "SET...SELECT...WHERE" logic to ensure that just the rows that return from the SELECT get modified in the UPDATE statement (otherwise, every row in your "APP_STAT" gets updated, with the "DECISION" and "DECISION_DATE" columns becoming NULL if there is no match in your "SET...SELECT...WHERE" clause(s).

After you have had a chance to inspect and digest the above, let us know if you have additional questions, and let us know how the execution of the code worked (or didn't work) for you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[sxschech]

Not sure if I'm supposed to change anything...got this error when trying to run:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_X'

 

[Turkbear]

Hi,
using X as an alias can cause that error...
( Santa will probably have more details on this)

See:

http://forums.oracle.com/forums/thread.jspa?threadID=333254

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Actually, Turkbear, this falls into the "YLSNED" (You Learn Something New Every Day) category...I had no idea that "X" was a verboten alias.

So, Sxschech, change x to something non-x as a workaround, then re-try (if you haven't done so already), and let us know the outcome.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[sxschech]

Tried it. No luck yet. I changed x to AP

New Error: ORA-00904: "AP"."PERSON_UID": invalid identifier.

I then did a select as below and this did not cause error:

select ap.person_uid from app_stat ap;

 

[Turkbear]

Hi,
Which AP.PERSONID reference triggered the error or was it the first time it was referenced?

( One assumes that there IS an personid field in app_stat)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi again,
Ignore my typo on the field name - PERSON_UID I meant to type..





To Paraphrase:"The Help you get is proportional to the Help you give.."