Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    Unanswered: PL/SQL update with inner join

    I having a real difficult time with something that shouldnt be that hard:
    I have two schemas - Master contains the master tables and Prod contains the dynamic ones. Here's the question:


    Schema: MASTER
    LOOKUP_TABLE:
    Error_Code_Num (Unique number)
    Error_Code_Desc (text description)

    Schema: PROD
    VALIDATION_TABLE:
    Detail_Comments (Text)
    Error_Code (Number)

    I want to update the validation table and place Error_Code_Desc in Detail_Comments when the LOOKUP_TABLE.Error_Code <>0 and the LOOKUP_TABLE.Error_code_num = Validation_Table.error_code


    I keep trying stuff like:
    UPDATE VALIDATION_TABLE INNER JOIN MASTER.LOOKUPS A
    ON A.LOOKUP_CODE_NUM = VALIDATION_TABLE.ERROR_CODE
    SET VALIDATION_TRUE_UP.DETAIL_COMMENT = A.Error_Code_Desc WHERE VALIDATION_TABLE.ERROR_CODE <> 0 ;

    It doesnt like that too much and keeps wanting me to place the set before the inner join.
    What Am I doing wrong?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >PL/SQL update with inner join
    1) what you posted is not PL/SQL
    2) why not just create a VIEW rather than physically duplicating data? (which IMO is a poor design)
    Rhetorical question - how/why do expect to obtain the comment string WITHOUT doing any SELECT?
    If you get really desperate you could try reading the fine SQL Reference manual which show valid syntax for UPDATE
    (found at http://tahiti.oracle.com)
    Last edited by anacedent; 01-19-07 at 19:36.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    First of all, I agree with anacedent : this is not PLSQL and you should read the documentation.

    As for the UPDATE, this should work :

    Code:
    UPDATE VALIDATION_TABLE V
    SET Detail_Comments = 
        (SELECT Error_Code_Desc
        FROM LOOKUP_TABLE
        WHERE Error_Code_Num = V.Error_Code)
    WHERE ERROR_CODE <> 0;
    Now, as anacedent said, it would be better to use a view in order to get the error description. Something like this :

    Code:
    CREATE OR REPLACE VIEW V_VALIDATION
    (
        Error_Code,
        Detail_Comments
    ) 
    AS
    (
        SELECT V.Error_Code,
            CASE V.Error_Code WHEN 0 THEN NULL ELSE L.Error_Code_Desc END
        FROM VALIDATION_TABLE V, LOOKUP_TABLE L
        WHERE V.Error_Code = L.Error_Code_Num
    );
    You would not need the error decription in the VALIDATION_TABLE anymore, so no update needed either.

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  4. #4
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    Thanks folks. Sorry about the misleading title.
    The code is part of my first PL/SQL package - I'm trying to export data. This was to just set it up.

    Thanks for the insight as to the view. Problem is that there are many comments coming from many sources. The logic is such that there is a priority. This would be difficult if not impossible to do in a view as they are all conditional on others as well as certain requirements.

    Dont be too harsh - it was an inherited app....
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •