Post reply

Update a table with an aggregate function

  • December 4, 2015 at 10:32 am

    #306862

    I have a table as below

    Table A

    ProbID 1

    Describe Test the theory

    TableAID 1

    and want to add 1 to TableAID, i.e. the next record depending on the ID of the previous record. So the next record should be

    ProbID 1

    Describe Test the theory

    TableAID 2

    with an update and when i try to do this with an aggregate function as MAX i get an error. Any suggestions.

    thank you.

  • December 4, 2015 at 10:50 am

    #1844119

    DECLARE @TableA TABLE (ProbID int, Describe varchar(100), TableAID int);

    INSERT @TableA VALUES (1,'Describe Test the theory',1);

    SELECT * FROM @TableA;

    UPDATE @TableA

    SET TableAID = TableAID+1;

    SELECT * FROM @TableA;

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

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

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