Date update

  • I am trying to use the SQL below

    UPDATE

    WMWHSE1.LOTATTRIBUTE

    SET LOTTABLE05 =

    (

    SELECT

    I.LOTTABLE05

    FROM WMWHSE1.ITRN I

    JOIN WMWHSE1.LOTATTRIBUTE L

    ON I.LOTTABLE06 = L.LOTTABLE06

    WHERE

    L.STORERKEY = '11871'

    AND L.SKU = '243795'

    )

    Getting this error.  Any suggestions?

    Msg 512, Level 16, State 1, Line 2

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    The statement has been terminated.

  • The error message tells you exactly what's wrong.  You need to rewrite the subquery so it returns only one value.

    John

  • The answer is in the error message, your subquery needs to return only ONE value, ooops didn't see Johns response!

    ...

  • DOUCETTR26 - Tuesday, February 26, 2019 1:15 AM

    I am trying to use the SQL below

    UPDATE

    WMWHSE1.LOTATTRIBUTE

    SET LOTTABLE05 =

    (

    SELECT

    I.LOTTABLE05

    FROM WMWHSE1.ITRN I

    JOIN WMWHSE1.LOTATTRIBUTE L

    ON I.LOTTABLE06 = L.LOTTABLE06

    WHERE

    L.STORERKEY = '11871'

    AND L.SKU = '243795'

    )

    Getting this error.  Any suggestions?

    Msg 512, Level 16, State 1, Line 2

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    The statement has been terminated.

    The following query is returning more than one record

    SELECT

    I.LOTTABLE05

    FROM WMWHSE1.ITRN I

    JOIN WMWHSE1.LOTATTRIBUTE L

    ON I.LOTTABLE06 = L.LOTTABLE06

    WHERE

    L.STORERKEY = '11871'

    AND L.SKU = '243795'

    )

  • I would write this more compactly
    UPDATE  WMWHSE1.LOTATTRIBUTE
      SET LOTTABLE05 =     I.LOTTABLE05
      FROM WMWHSE1.ITRN I

       JOIN WMWHSE1.LOTATTRIBUTE L

        ON I.LOTTABLE06 = L.LOTTABLE06

      WHERE

       L.STORERKEY = '11871'

      AND L.SKU = '243795'

    The error is what people posted above. SQL Server is getting multiple results back and can't decide which goes into a single cell.

  • I don't think that you want to be using a sub-query at all.  Try the following.

    UPDATE L
    SET LOTTABLE05 = I.LOTTABLE05
    FROM WMWHSE1.ITRN I
    JOIN WMWHSE1.LOTATTRIBUTE L
        ON I.LOTTABLE06 = L.LOTTABLE06
    WHERE L.STORERKEY = '11871'
        AND L.SKU = '243795'

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply