Post reply

TSQL ALternative help Please - Setbased Approach if possible

  • November 10, 2014 at 12:24 pm

    #298330

    Hi All,

    I would appreciate some help with an alternative syntax for the SQL Statement below:

    UPDATE [dbo].[TableA]

    SET

    sysReturnDate_Current =

    CASE WHEN Frequency = 'Quarterly' AND ReturnDate IS NOT NULL THEN

    (SELECT DATEADD(qq,1,ReturnDate)

    FROM [dbo].[TableA]

    WHERE [CurrentRecord] = 1) END

    The statement is used to update another field with another column within the same table, if the fields:

    Frequency ='Quarterly' AND

    ReturnDate IS NOT NULL.

    However, there could be many rows of records in the table with the field [CurrentRecord] = 1. This causes the error to be generated:

    Msg 512, Level 16, State 1, Line 1

    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.

    I could make a variable and add the key column and do this one at a time.

    But if you could suggest a set-based approach that would be great.

  • November 10, 2014 at 12:43 pm

    #1757536

    Try:

    UPDATE dbo.TableA

    SET sysReturndate_Current = DATEADD(qq, 1, ReturnDate)

    WHERE CurrentRecord = 1 AND Frequency = 'Quarterly' AND ReturnDate IS NOT NULL;

    Gerald Britton, Pluralsight courses

  • November 11, 2014 at 3:20 am

    #1757632

    Thank you

Viewing 3 posts - 1 through 2 (of 2 total)

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