Help with an upsert

  • Below is some example data. First, I pull everything from the table person. Then, I need to check if that personid already exists in the table process with the activityid of 991. If this is true then the fieldvalue and datelstmod can be updated if the field value is different.

    If the record does not exist in the table process then the row can be inserted with an activityid of 991.

    CREATE TABLE process
    (
        personid int,
        activityid int,
        fieldvalue varchar(25),
        dateadded datetime2,
        datelstmod datetime2,
        processid int identity(1,1)
    )

    INSERT INTO process VALUES
    (111, 991, 'Yes', getdate(), getdate()),
    (111, 890, 'Other', getdate(), getdate()),
    (222, 991, 'Yes', getdate(), getdate())

    CREATE TABLE person
    (
        personid int,
        requestind varchar(25)
    )

    INSERT INTO person VALUES
    (111, 'Yes'),
    (333, 'Yes'),
    (222, 'No'),
    (444, 'No'),
    (888, 'Yes'),
    (999, 'Yes')

    The results would then look something like this:
    111    991    Yes    2018-05-03 12:51:40.0500000    2018-05-03 12:51:40.0500000    1
    111    890    Other    2018-05-03 12:51:40.0500000    2018-05-03 12:51:40.0500000    2
    222    991    No    2018-05-03 12:51:40.0500000    2018-05-03 12:56:60.0100000    3
    888    991    Yes    2018-05-03 12:56:60.0100000    2018-05-03 12:56:60.0100000    4
    999    991    Yes    2018-05-03 12:56:60.0100000    2018-05-03 12:56:60.0100000    5
    444    991    No    2018-05-03 12:56:60.0100000    2018-05-03 12:56:60.0100000    6
    333    991    Yes    2018-05-03 12:56:60.0100000    2018-05-03 12:56:60.0100000    7

  • UPDATE prc
    SET fieldvalue = prs.requestind
      , prc.datelstmod = GetDate()
    FROM dbo.process prc
    JOIN dbo.person prs
    ON prc.personid = prs.personid;


  • UPDATE pr
    SET fieldvalue = pe.requestind,
        datelstmod = SYSDATETIME()
    FROM process pr
    INNER JOIN person pe ON pe.personid = pr.personid AND pr.activityid = 991
    WHERE pr.fieldvalue <> pe.requestind

    INSERT INTO process ( personid, activityid, fieldvalue, dateadded )
    SELECT pe.personid, 991, pe.requestind, SYSDATETIME()
    FROM person pe
    WHERE NOT EXISTS(
        SELECT 1
        FROM process pr
        WHERE pr.personid = pe.personid AND pr.activityid = 991)

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Joe Torre - Thursday, May 3, 2018 12:44 PM

    UPDATE prc
    SET fieldvalue = prs.requestind
      , prc.datelstmod = GetDate()
    FROM dbo.process prc
    JOIN dbo.person prs
    ON prc.personid = prs.personid;

    Thanks for the suggestion. However, I think this would miss the records that need to be inserted because they aren't present to update.

  • ScottPletcher - Thursday, May 3, 2018 12:58 PM


    UPDATE pr
    SET fieldvalue = pe.requestind,
        datelstmod = SYSDATETIME()
    FROM process pr
    INNER JOIN person pe ON pe.personid = pr.personid AND pr.activityid = 991
    WHERE pr.fieldvalue <> pe.requestind

    INSERT INTO process ( personid, activityid, fieldvalue, dateadded )
    SELECT pe.personid, 991, pe.requestind, SYSDATETIME()
    FROM person pe
    WHERE NOT EXISTS(
        SELECT 1
        FROM process pr
        WHERE pr.personid = pe.personid AND pr.activityid = 991)


    Thanks. I'll give this a shot in my dev environment.

  • You're welcome, let me know how it goes.

    Btw, it's inconsistent to use data type "datetime2" and GETDATE(), which returns only a "datetime" value.  That's why I used SYSDATETIME() in my code.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Great, thanks for the tip!

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

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