Post reply

How to perform an update to an old record when inserting new record in same table using merge, when the ON clause matches only some of the key fields.

  • December 8, 2011 at 8:56 am

    #264433

    I have a table that contains records which has a primary key consisting of a memberID, enrollmentID and enrollmentStartDate. In that table there is a field call primaryflag that is set to 1 if it is the member's primary enrollment record and needs to be set to 0 when a new record with the same memberID, but new enrollmentID and enrollmentStartDate is inserted into the table. How can I do this with either the MERGE functionality or some other update process in T-SQL. I thought I was pretty good at T-SQL, but this has me stumped. The problem is that a member could have any number of enrollments even if the enrollmentTermDate is long gone, but the member can only have 1 primary enrollment. All enrollment history has to be kept in the same table.

    Any help will be greatly appreciated.

    Thank you.

    Hawkeye

    Hawkeye67

  • December 8, 2011 at 8:59 am

    #1418743

    Additional information: I only want to updated the most recent existing record and not all of the existing records pertaining to the target memberID. Otherwise, I am updating all of the records in a very large table consisting of 15 - 20 million records.

    Hawkeye67

  • December 8, 2011 at 10:24 am

    #1418816

    You could use the OUTPUT clause:

    DROP TABLE #MemberEnrolments

    CREATE TABLE #MemberEnrolments (MemberID INT NOT NULL, EnrollmentID INT NOT NULL, enrolmentStartDate DATE NOT NULL, primaryflag BIT)

    ALTER TABLE #MemberEnrolments --WITH NOCHECK

    ADD CONSTRAINT PK_MemberID_EnrollmentID_enrollmentStartDate PRIMARY KEY CLUSTERED (MemberID, EnrollmentID, enrolmentStartDate)

    DROP TABLE #inserts

    CREATE TABLE #inserts (MemberID INT NOT NULL, EnrollmentID INT NOT NULL, enrolmentStartDate DATE NOT NULL)

    INSERT INTO #MemberEnrolments (MemberID, EnrollmentID, enrolmentStartDate, primaryflag)

    OUTPUT inserted.MemberID, inserted.EnrollmentID, inserted.enrolmentStartDate INTO #inserts

    SELECT 1,11,GETDATE()-20,1 UNION ALL

    SELECT 2,4,GETDATE()-10,1

    UPDATE m SET primaryflag = 0

    FROM #MemberEnrolments m

    INNER JOIN (

    SELECT m.MemberID, EnrollmentID = MAX(m.EnrollmentID)

    FROM #MemberEnrolments m

    INNER JOIN #inserts i ON i.MemberID = m.MemberID AND m.EnrollmentID < i.EnrollmentID

    GROUP BY m.MemberID

    ) x ON x.MemberID = m.MemberID AND x.EnrollmentID = m.EnrollmentID

    SELECT * FROM #MemberEnrolments

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • December 8, 2011 at 11:04 am

    #1418834

    Thank you Chris M. Will be giving this a try later today. Will keep you posted on how I do. Thanks again.

    Hawkeye67

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

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