Results 1 to 6 of 6

Thread: SQL Question

  1. #1
    Join Date
    Oct 2012
    Posts
    21

    Question Unanswered: SQL Question

    I have multiple tables with an identical column which need to be populated using data a cached table.

    Cached table/view MAX006P.VMAX300M_ALL_USERIDS_ALL_COLUMNS

    Error:
    SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row.

    SQL:

    UPDATE COLLECT.TCOLECT070_REQUIRED_APPROVER T070
    SET (MF_USERID) =
    (SELECT T300.MF_USERID
    FROM MAX006P.VMAX300M_ALL_USERIDS_ALL_COLUMNS T300
    JOIN COLLECT.TCOLECT070_REQUIRED_APPROVER T070
    ON LCASE(T300.LDAP_EMAIL) = LCASE(T070.NOTIFYEE_EMAIL))
    WHERE T070.MF_USERID = ''

    My work around was to use a shell program to the updates one at a time since the result tables pulls back over 8 thousand rows.

    However, I wanted to know if this could be fixed using SQL

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Obviously, the join condition `ON LCASE(T300.LDAP_EMAIL) = LCASE(T070.NOTIFYEE_EMAIL)` is not sufficient to identify a single record, so you'd need to add some other condition(s).
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Oct 2012
    Posts
    21
    I limited this using the DISTINCT function in the subselect but still no success.

    UPDATE COLLECT.TCOLECT170_REQUIRED_APPROVER_HISTORY T070
    SET (MF_USERID) =
    (SELECT DISTINCT T300.MF_USERID--, T070.NOTIFYEE_EMAIL, T300.EMAIL
    FROM MAX006P.TMAX300_USER T300
    JOIN
    COLLECT.TCOLECT170_REQUIRED_APPROVER_HISTORY T070
    ON T300.LDAP_EMAIL = LCASE (T070.NOTIFYEE_EMAIL))
    WHERE T070.MF_USERID = ''

    Any idea how I might recode this?

    Thanks in advance.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    not without knowing your table structures and unique indexes.

    Dave

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Try by removing JOIN, like...
    Code:
    UPDATE COLLECT.TCOLECT170_REQUIRED_APPROVER_HISTORY T070
     SET   MF_USERID
         = (SELECT T300.MF_USERID
             FROM  MAX006P.TMAX300_USER T300
             WHERE T300.LDAP_EMAIL = LCASE(T070.NOTIFYEE_EMAIL)
           )
     WHERE MF_USERID = ''
    Last edited by tonkuma; 07-04-13 at 05:32.

  6. #6
    Join Date
    Oct 2012
    Posts
    21
    tonkuma: That did the trick. Thanks a mill.

Posting Permissions

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