Results 1 to 4 of 4

Thread: Sqlcode = -811

  1. #1
    Join Date
    May 2002
    Posts
    12

    Unanswered: Sqlcode = -811

    I have 2 tables:

    JOB_CODE Table:
    - JOB_CODE_ID
    - JOB_CODE
    - JOB_CODE_SUPER_ID
    - ...

    JDROOT Table:
    - JOB_CODE
    - SR_JOB_CODE

    I want to update JOB_CODE_SUPER_ID with the JOB_CODE_ID of the SR_JOB_CODE from JDROOT table.

    This is my SQL:

    UPDATE JOB_CODE A
    SET JOB_CODE_SUPER_ID =
    (SELECT B.JOB_CODE_ID
    FROM JOB_CODE B, JDROOT C
    WHERE C.JOB_CODE = A.JOB_CODE
    AND B.JOB_CODE = C.SR_JOB_CODE)
    WHERE A.JOB_CODE NOT IN (SELECT D.JOB_CODE
    FROM JDROOT
    WHERE D.JOB_CODE = D.SR_JOB_CODE)

    and I got this error message:

    SQLCODE = -811, ERROR: THE RESULT OF AN EMBEDDED SELECT STATEMENT OR A SUBSELECT IN THE SET CLAUSE OF AN UPDATE STATEMENT IS A TABLE OF MORE THAN ONE ROW, OR THE RESULT OF A SUBQUERY OF A BASIC PREDICATE IS MORE THAN VALUE

    Would you please help me... Thank you very much!!!

  2. #2
    Join Date
    May 2002
    Posts
    10
    Try to run this query alone and see how many rows you get
    SELECT B.JOB_CODE_ID
    FROM JOB_CODE B, JDROOT C
    WHERE C.JOB_CODE = A.JOB_CODE
    AND B.JOB_CODE = C.SR_JOB_CODE)
    WHERE A.JOB_CODE NOT IN (SELECT D.JOB_CODE
    FROM JDROOT
    WHERE D.JOB_CODE = D.SR_JOB_CODE)

    If you get more than one row , that means the update statement will not work.

    To reduce it to only one row add "FETCH FIRST 1 row only" clause at the end.(Be careful using this clause)

    SELECT B.JOB_CODE_ID
    FROM JOB_CODE B, JDROOT C
    WHERE C.JOB_CODE = A.JOB_CODE
    AND B.JOB_CODE = C.SR_JOB_CODE)
    WHERE A.JOB_CODE NOT IN (SELECT D.JOB_CODE
    FROM JDROOT
    WHERE D.JOB_CODE = D.SR_JOB_CODE)
    Fetch first 1 row only

  3. #3
    Join Date
    Jan 2002
    Location
    Manila, Philippines
    Posts
    71
    Your inner query is returning multiple records, hence the -811

    I suggest you check your logic ...

    UPDATE JOB_CODE A
    SET JOB_CODE_SUPER_ID =
    ***** your updating job code super id in job code table
    (SELECT B.JOB_CODE_ID
    ***** using the job code id you got from job code table
    FROM JOB_CODE B, JDROOT C


    If this is what you want, just do...

    UPDATE JOB_CODE SET JOB_CODE_SUPER_ID = JOB_CODE_ID

    I don't think this is what you want to do from what you've indicated, I would suggest doing POSITIONED UPDATE using CURSORS.

    HTH,
    Oliver

  4. #4
    Join Date
    Jun 2002
    Posts
    7
    Hi, The prb you get more than 1 row from the sub query. In order to check if this is the prb try to add function like max or min or sum. if this will work you probably get more than 1 row from the sub select.

Posting Permissions

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