Update query - ora-01427 error
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Update query - ora-01427 error

  1. #1
    Join Date
    Jan 2008
    Posts
    5

    Update query - ora-01427 error

    Hello,
    I'm trying to run an update query, to replace all rows with a null date value, with a date from another table.
    I get an ora-01427 error when I try to run the query.
    I realize that the reason I get this is because there is >1 row being returned - but there are >1 records I need updated.
    I'm thinking I need to use 'IN' instead of '=' but not sure how...
    I've pasted my code below and attached a .pdf explanation (it's quite confusing).
    Thanks in advance!!
    Amber

    UPDATE SILVICULTURE_ACTIVITY
    SET SILA_START_DATE = (SELECT SILVICULTURE_AMENDMENT.SAMM_DIST_APPR_DATE
    FROM
    SILVICULTURE_ACTIVITY,
    SILVICULTURE_COMPANY_ACTIVITY,
    SILVICULTURE_AMENDMENT,
    SILVICULTURE_PRESCRIPTION,
    CUT_BLOCK
    WHERE
    SILVICULTURE_ACTIVITY.SICA_SEQ_NBR = SILVICULTURE_COMPANY_ACTIVITY.SICA_SEQ_NBR AND
    SILVICULTURE_ACTIVITY.CUTB_SEQ_NBR = SILVICULTURE_PRESCRIPTION.CUTB_SEQ_NBR AND
    SILVICULTURE_AMENDMENT.SILP_SEQ_NBR = SILVICULTURE_PRESCRIPTION.SILP_SEQ_NBR AND
    SILVICULTURE_PRESCRIPTION.CUTB_SEQ_NBR = CUT_BLOCK.CUTB_SEQ_NBR AND
    SICA_ACTIVITY_NAME = 'SP' AND
    SILVICULTURE_AMENDMENT.SAMM_DIST_APPR_DATE IS NOT NULL)
    WHERE
    SILA_STATUS = 'P' AND SILA_START_DATE IS NULL AND
    /
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You want to update more than one row on SILVICULTURE_ACTIVITY, but you cannot update each SILVICULTURE_ACTIVITY.SILA_START_DATE column with multiple values like your query is trying to do.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jan 2008
    Posts
    5
    I realize that.
    What I don't know is how to fix it...

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    The problem is that you are including the updating table in the FROM clause of the sub-SELECT.

    Removing that gives you:
    Code:
    UPDATE   
       SILVICULTURE_ACTIVITY   SAC
    SET   
       SAC.SILA_START_DATE   =   
          (
          SELECT   
             SAM.SAMM_DIST_APPR_DATE
          FROM
             SILVICULTURE_COMPANY_ACTIVITY     SCA,
             SILVICULTURE_PRESCRIPTION         SP ,
             SILVICULTURE_AMENDMENT            SAM,
             CUT_BLOCK                         CB
          WHERE
             SCA.SICA_SEQ_NBR        =   SAC.SICA_SEQ_NBR   AND
             SP.CUTB_SEQ_NBR         =   SAC.CUTB_SEQ_NBR   AND
             SAM.SILP_SEQ_NBR        =   SP.SILP_SEQ_NBR    AND
             CB.CUTB_SEQ_NBR         =   SP.CUTB_SEQ_NBR    AND
             SICA_ACTIVITY_NAME      =   'SP'               AND
             SAM.SAMM_DIST_APPR_DATE IS   NOT NULL
          )
    WHERE
       SAC.SILA_STATUS      =   'P'   AND   
       SAC.SILA_START_DATE  IS   NULL
    However, since the date you are attempting to get at is in the SAM table, I don't know why you feel you need to include the SCA and CB tables in the sub-SELECT. Of course, you don't say what table the SICA_ACTIVITY_NAME column is in, so maybe one of those two tables has a purpose.
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  5. #5
    Join Date
    Jan 2008
    Posts
    5
    In my original post, I attached a .pdf to clarify how my tables are linked, and what I'm trying to do.
    This would answer your questions.
    Thanks,
    Amber

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Quote Originally Posted by amberh
    In my original post, I attached a .pdf to clarify how my tables are linked, and what I'm trying to do.
    This would answer your questions.
    Thanks,
    Amber
    Yeah - missed that the first time - wow, but you are confused.

    Okay, so try this:

    Code:
    UPDATE   
       SILVICULTURE_ACTIVITY   SAC
    SET
          -- Update the start date   
       SAC.SILA_START_DATE   =   
          (
          SELECT   
             SAM.SAMM_DIST_APPR_DATE
          FROM
             SILVICULTURE_PRESCRIPTION         SP ,
             SILVICULTURE_AMENDMENT            SAM
          WHERE
             SP.CUTB_SEQ_NBR         =   SAC.CUTB_SEQ_NBR   AND
             SAM.SILP_SEQ_NBR        =   SP.SILP_SEQ_NBR    
          )
    WHERE
          -- When status is P,
       SAC.SILA_STATUS      =   'P'     AND   
          -- start date is NULL,
       SAC.SILA_START_DATE  IS   NULL   AND
          -- and activity is SP
       SAC.SICA_SEQ_NBR    IN
          (
          SELECT
             SCA.SICA_SEQ_NBR
          FROM
             SILVICULTURE_COMPANY_ACTIVITY     SCA
          WHERE
             SCA.SICA_SEQ_NBR        =   SAC.SICA_SEQ_NBR   AND
             SCA.SICA_ACTIVITY_NAME  =   'SP'               
          )
    Note that the requirement of AMENDMENT.Samm_Dist_Appr_Date ≠ Null is extraneous.

    Basically, if the date is already NULL, setting it to another NULL changes nothing, and testing to make sure the source date is not NULL before the record is updated is more work. So, unless you expect a significant number of NULLs, it won't be worth the extra effort.

    And, as I said before, the CUT_BLOCK table is completely unnecessary here.
    Last edited by chrisrlong; 10-02-2008 at 11:30 PM.
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  7. #7
    Join Date
    Jan 2008
    Posts
    5
    When I run the select SQL pasted at the end of this post, I return 822 records. So for these records, I want to replace the SILA_COMPLETION_DATE with the SAMM_DIST_APPR_DATE.

    I tried your code (with a copy of my database) and ended up with a total of 2974 records changed.
    And it seemed to have replaced all the dates with the same date - not the related date in the Amendment table.

    Thanks,
    Amber

    SELECT
    CUT_PERMIT.PERM_PERMIT_ID,
    CUT_BLOCK.CUTB_BLOCK_ID,
    SILVICULTURE_ACTIVITY.SILA_STATUS,
    SILVICULTURE_ACTIVITY.SILA_COMPLETION_DATE,
    SILVICULTURE_COMPANY_ACTIVITY.SICA_ACTIVITY_NAME,
    SILVICULTURE_AMENDMENT.SAMM_LICENSEE_APPR_DATE,
    SILVICULTURE_AMENDMENT.SAMM_DIST_APPR_DATE
    FROM
    CUT_PERMIT,
    BLOCK_ALLOCATION,
    SILVICULTURE_ACTIVITY,
    SILVICULTURE_COMPANY_ACTIVITY,
    SILVICULTURE_AMENDMENT,
    SILVICULTURE_PRESCRIPTION,
    CUT_BLOCK
    WHERE
    CUT_PERMIT.PERM_SEQ_NBR = BLOCK_ALLOCATION.PERM_SEQ_NBR AND
    BLOCK_ALLOCATION.CUTB_SEQ_NBR = CUT_BLOCK.CUTB_SEQ_NBR AND
    SILVICULTURE_ACTIVITY.SICA_SEQ_NBR = SILVICULTURE_COMPANY_ACTIVITY.SICA_SEQ_NBR AND
    SILVICULTURE_ACTIVITY.CUTB_SEQ_NBR = SILVICULTURE_PRESCRIPTION.CUTB_SEQ_NBR AND
    SILVICULTURE_AMENDMENT.SILP_SEQ_NBR = SILVICULTURE_PRESCRIPTION.SILP_SEQ_NBR AND
    SILVICULTURE_PRESCRIPTION.CUTB_SEQ_NBR = CUT_BLOCK.CUTB_SEQ_NBR AND
    SILA_STATUS = 'P' AND
    SICA_ACTIVITY_NAME = 'SP' AND
    SILA_START_DATE IS NULL AND
    SILVICULTURE_AMENDMENT.SAMM_AMENDMENT_NBR = '0' AND
    SILVICULTURE_AMENDMENT.SAMM_DIST_APPR_DATE IS NOT NULL
    /

  8. #8
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Well, let's see - you added new tables and new predicates and are wondering why the numbers are different. Are you serious?

    Code:
    SELECT
       CP.PERM_PERMIT_ID          ,
       CB.CUTB_BLOCK_ID           ,
       SAC.SILA_STATUS            ,
       SAC.SILA_COMPLETION_DATE   ,
       SCA.SICA_ACTIVITY_NAME     ,
       SAM.SAMM_LICENSEE_APPR_DATE,
       SAM.SAMM_DIST_APPR_DATE
    FROM
       CUT_PERMIT                     CP  ,
       BLOCK_ALLOCATION               BA  ,
       SILVICULTURE_ACTIVITY          SAC ,
       SILVICULTURE_COMPANY_ACTIVITY  SCA ,
       SILVICULTURE_AMENDMENT         SAM ,
       SILVICULTURE_PRESCRIPTION      SP  ,
       CUT_BLOCK                      CB
    WHERE
          -- When status is P,
       SAC.SILA_STATUS          =   'P'                  AND
          -- start date is NULL,
       SAC.SILA_START_DATE      IS  NULL                 AND
       SCA.SICA_SEQ_NBR         =   SAC.SICA_SEQ_NBR     AND
          -- and activity is SP
       SCA.SICA_ACTIVITY_NAME   =   'SP'                 AND
    
          -- What is all this nonsense??
       CP.PERM_SEQ_NBR          =   BA.PERM_SEQ_NBR      AND
       BA.CUTB_SEQ_NBR          =   CB.CUTB_SEQ_NBR      AND
       SAC.CUTB_SEQ_NBR         =   SP.CUTB_SEQ_NBR      AND
       SAM.SILP_SEQ_NBR         =   SP.SILP_SEQ_NBR      AND
       SP.CUTB_SEQ_NBR          =   CB.CUTB_SEQ_NBR      AND
          -- Where did this come from?
       SAM.SAMM_AMENDMENT_NBR   =   '0'                  AND
          -- I already covered why this was not used
       SAM.SAMM_DIST_APPR_DATE  IS  NOT NULL
    Where did CUT_PERMIT come from?
    Where did SAMM_AMENDMENT_NBR = '0' come from?

    Quote Originally Posted by amberh
    And it seemed to have replaced all the dates with the same date - not the related date in the Amendment table.
    Huh?

    The UPDATE statement only targetted those rows where the SILA_START_DATE is NULL, so it shouldn't have replaced any actual 'date' with anything. Are you saying that absolutely none of the values were populated?
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  9. #9
    Join Date
    Jan 2008
    Posts
    5
    You misunderstood.
    If you look closely - the SQL in my last post is a SELECT query - not an UPDATE query.
    I added this to my last post for clarification. This query correctly shows how many rows I needed to change.

    CUT_PERMIT was simply there for clarification (for me) - it wasn't part of the UPDATE query.

    The SAMM_AMENDMENT_NBR = '0' was criteria I realized was missing - it is what ensures that there is only one record in the SILVICULTURE_AMENDMENT table for each record in the SILVICULTURE_ACTIVITY table.

    I should have said it replaced all the NULL values with a single date, not the related date from the Amendment table.

  10. #10
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Okay, let's see if I can explain this a little better.

    First, your addition of extra tables and predicates changes the number of rows in the resultset.

    You are adding three tables to the statement that have absolutely nothing to do with the functionality of the statement:

    Code:
    SELECT
       ...
    FROM
       SILVICULTURE_ACTIVITY          SAC ,
       SILVICULTURE_COMPANY_ACTIVITY  SCA ,
       SILVICULTURE_PRESCRIPTION      SP  ,
       SILVICULTURE_AMENDMENT         SAM ,
          -- These tables are irrelevant and actually problematic
       CUT_BLOCK                      CB  ,
       BLOCK_ALLOCATION               BA  
       CUT_PERMIT                     CP  ,
    WHERE
       ... AND
       CB.CUTB_SEQ_NBR          =   SAC.CUTB_SEQ_NBR      AND
       BA.CUTB_SEQ_NBR          =   SAC.CUTB_SEQ_NBR      AND
       CP.PERM_SEQ_NBR          =   BA.PERM_SEQ_NBR
    What you are in effect saying is that you don't want to modify (or select) every row in SAC. You only want to modify those that have a CUTB_SEQ_NBR value that not only exists in the SP table, but *also* exists in *both* the CB and BA tables. And further, to only those values that not only exist in the BA table, but to only those records in the BA table that also have PERM_SEQ_NBR values that exist in the CP table. We have now quite a few restrictions that my statement did not take into account.

    Second, the UPDATE statement I provided was also not restricted to those rows where SAM.SAMM_DIST_APPR_DATE IS NOT NULL. The reasoning was that, since you are updating a column that is already NULL, setting it to NULL was not harmful, and the extra work to do said restriction was likely not worth the effort. This would also have restricted the number of rows in your resultset as compared to mine. But since this obviously confused you, I will add it to my statement.

    Now, with the appropriate changes made to both statements, they should select / modify the same number of rows.

    Code:
    SELECT
       CP.PERM_PERMIT_ID          ,
       CB.CUTB_BLOCK_ID           ,
       SAC.SILA_STATUS            ,
       SAC.SILA_COMPLETION_DATE   ,
       SCA.SICA_ACTIVITY_NAME     ,
       SAM.SAMM_LICENSEE_APPR_DATE,
       SAM.SAMM_DIST_APPR_DATE
    FROM
       SILVICULTURE_ACTIVITY          SAC ,
       SILVICULTURE_COMPANY_ACTIVITY  SCA ,
       SILVICULTURE_PRESCRIPTION      SP  ,
       SILVICULTURE_AMENDMENT         SAM ,
       CUT_BLOCK                      CB  ,
       BLOCK_ALLOCATION               BA  ,
       CUT_PERMIT                     CP  
    WHERE
          -- When status is P,
       SAC.SILA_STATUS          =   'P'                  AND
          -- start date is NULL,
       SAC.SILA_START_DATE      IS  NULL                 AND
       SCA.SICA_SEQ_NBR         =   SAC.SICA_SEQ_NBR     AND
          -- activity is SP,
       SCA.SICA_ACTIVITY_NAME   =   'SP'                 AND
    
       SP.CUTB_SEQ_NBR          =   SAC.CUTB_SEQ_NBR     AND
       SAM.SILP_SEQ_NBR         =   SP.SILP_SEQ_NBR      AND
          -- amendment number is 0,
       SAM.SAMM_AMENDMENT_NBR   =   '0'                  AND
          -- and target date is not null
       SAM.SAMM_DIST_APPR_DATE  IS  NOT NULL             AND
    
          -- Let's change these to outer-joins so they don't restrict anything
       CB.CUTB_SEQ_NBR       (+)=   SAC.CUTB_SEQ_NBR     AND
       BA.CUTB_SEQ_NBR       (+)=   SAC.CUTB_SEQ_NBR     AND
       CP.PERM_SEQ_NBR       (+)=   BA.PERM_SEQ_NBR
    Code:
    UPDATE
       SILVICULTURE_ACTIVITY   SAC
    SET
          -- Update the start date
       SAC.SILA_START_DATE   =
          (
          SELECT
             SAM.SAMM_DIST_APPR_DATE
          FROM
             SILVICULTURE_PRESCRIPTION         SP ,
             SILVICULTURE_AMENDMENT            SAM
          WHERE
             SP.CUTB_SEQ_NBR          =   SAC.CUTB_SEQ_NBR     AND
             SAM.SILP_SEQ_NBR         =   SP.SILP_SEQ_NBR      AND
                -- amendment number is 0,
             SAM.SAMM_AMENDMENT_NBR   =   '0'                  AND
                -- and target date is not null
             SAM.SAMM_DIST_APPR_DATE  IS  NOT NULL             
          )
    WHERE
          -- When status is P,
       SAC.SILA_STATUS      =   'P'     AND
          -- start date is NULL,
       SAC.SILA_START_DATE  IS   NULL   AND
          -- activity is SP,
       SAC.SICA_SEQ_NBR    IN
          (
          SELECT
             SCA.SICA_SEQ_NBR
          FROM
             SILVICULTURE_COMPANY_ACTIVITY     SCA
          WHERE
             SCA.SICA_SEQ_NBR        =   SAC.SICA_SEQ_NBR   AND
             SCA.SICA_ACTIVITY_NAME  =   'SP'               
          )                             AND
       EXISTS(
          SELECT
             1
          FROM
             SILVICULTURE_PRESCRIPTION      SP  ,
             SILVICULTURE_AMENDMENT         SAM 
          WHERE
             SP.CUTB_SEQ_NBR          =   SAC.CUTB_SEQ_NBR     AND
             SAM.SILP_SEQ_NBR         =   SP.SILP_SEQ_NBR      AND
                -- amendment number is 0,
             SAM.SAMM_AMENDMENT_NBR   =   '0'                  AND
                -- and target date is not null
             SAM.SAMM_DIST_APPR_DATE  IS  NOT NULL             
          )

    As for all the rows getting set to the same date - I don't see how that was possible, unless all those dates in the SAM table just happen to all be the same. Please check the results again and let me know what you find.
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

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


Click Here to Expand Forum to Full Width