Post reply

Syntax update derived tables

  • April 12, 2007 at 9:15 am

    #118171

    Can someone tell me how to do this -
     

             UPDATE B  SET kMembStatusCode = 2,  StatusDate = GetDate() FROM  (SELECT OrganisationID FROM OrgsToAppend WHERE Member = 1) A

         

            INNER JOIN

         

            (SELECT OrganisationID, kMembStatusCode, StatusDate FROM Membership WHERE kOtherClassDescID = @IDTo AND kMembStatusCode = @Term) B

         

            ON A.OrganisationID = B.OrganisationID

     
     
    If I try and run it I get Err #4421 Derived table 'B' is not updateable because a column of the derived table is derived or constant.
     
    Thanks

  • April 12, 2007 at 9:33 am

    #700133

    Try:

    UPDATE B

    SET kMembStatusCode = 2

        ,StatusDate = GETDATE()

    FROM Membership B

        JOIN OrgsToAppend A

            ON B.OrganisationID = A.OrganisationID

    WHERE A.Member = 1

        AND B.kOtherClassDescID = @IDTo

        AND B.MembStatusCode = @Term

     

  • April 12, 2007 at 9:37 am

    #700134

    or:

    UPDATE Membership

    SET kMembStatusCode = 2

        ,StatusDate = GETDATE()

    WHERE EXISTS (

            SELECT *

            FROM OrgsToAppend A

            WHERE Membership.OrganisationID = A.OrganisationID

                AND A.Member = 1

        )

        AND kOtherClassDescID = @IDTo

        AND MembStatusCode = @Term

  • April 12, 2007 at 9:42 am

    #700138

    Thanks - that worked - so I just need to move my criteria outside of the derived table.
     
    Can I not get differant results by moving the criteria (I mean generally rather than the above example)
     
    Thanks again

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

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