Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    16

    Unanswered: Need Help with an Update Query

    UPDATE AFEDETAILS a SET (a."ACCOUNT", a."SUBACCOUNT", a."CLASSIFICATION")=
    (SELECT x."NewAcct", x."NewSub", x."New_Description"
    FROM AFE_XREF_0381 x
    WHERE a."ACCOUNT" = x."Account"
    AND a."SUBACCOUNT" = x."SubAccount")
    WHERE a."TEMPLATE" = 'Workover'
    AND a."PROJECT" = 'C00A1000'


    I have imported and XREF table into Oracle and would like to use that to update my existing table (AFEDETAILS)

    However when I run the above query, I get the following error:
    ORA-01407 - Cannot Update ("PARADOX","AFEDETAILS","ACCOUNT") To NULL

    I have checked the data and I don't have any fields that are null.

    Can someone help me out with this query.

    Thanks,
    pbare

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    you need a where clause for the update itself (not the subquery of the set) to check XREF. you're updating rows which have no matches, so oracle tries to set those rows to null

    UPDATE AFEDETAILS a SET (a."ACCOUNT", a."SUBACCOUNT", a."CLASSIFICATION")=
    (SELECT x."NewAcct", x."NewSub", x."New_Description"
    FROM AFE_XREF_0381 x
    WHERE a."ACCOUNT" = x."Account"
    AND a."SUBACCOUNT" = x."SubAccount")
    WHERE a."TEMPLATE" = 'Workover'
    AND a."PROJECT" = 'C00A1000'
    -- add this:
    and exists
    (SELECT null FROM AFE_XREF_0381 x
    WHERE a."ACCOUNT" = x."Account"
    AND a."SUBACCOUNT" = x."SubAccount")
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

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