EXISTS

  • I'm writing a TSQL sproc that will be called by client applications to save a row to a table. The idea is that if the record being saved already exists, update the row, otherwise insert it. I'm trying to decide which is better:

    IF EXISTS(

    SELECT * FROM AwardsTable WHERE cutomerCode = @custCode AND busDate = @busDate

    )

    DELETE FROM AwardsTable WHERE CustomerCode = @custCode AND BusDate = @busDate

    OR

    DELETE FROM AwardsTable WHERE CustomerCode = @custCode AND BusDate = @busDate

    .

  • I am unsure how your code relates to the requirement "The idea is that if the record being saved already exists, update the row, otherwise insert it."

    You appear to be performing a DELETE.

    MERGE is one compact option to perform an UPSERT.

    There are times when EXISTS produces a more efficient plan - but you have to be careful with it to guarantee a correct UPSERT. It depends on your data, and your priorities...

    Paul

  • Sorry about that Paul! I just got done editing the post.

    I bailed out in the middle of my posting and jumped over to another forum to ask how others are able to post well formatted code. I always struggle with that. I don't know how to do it, and I can't find help on that topic.

    .

  • Wrap your code in [ code ] [ /code ] tags (remove the spaces within the tags there)

    Click the 'IFCode' button in the editor (to the right of the Bold, Italic, Underline, and Quote buttons in the toolbar) for more tag options 🙂

    I saw your edit, and made my own!

  • Lets try this one more time. This post is definitely a sign it's time for bed!!

    I'm writing a TSQL sproc that will be called by client applications to save a row to a table. The idea is that if the record being saved already exists, update the row, otherwise insert it. I'm trying to decide which is better:

    if not exists(select bla, bla, bla)

    insert record here

    else

    update record here

    OR

    delete from target

    insert into target

    .

  • Demonstration code using MERGE:

    -- Test table

    DECLARE @test-2

    TABLE (col1 INT PRIMARY KEY, data INTEGER NOT NULL);

    -- A row to UPSERT

    INSERT @test-2 (col1, data) VALUES (1, 1);

    SELECT *

    FROM @test-2;

    -- UPSERT an existing value

    MERGE @test-2

    USING (

    VALUES (1, 100)

    )

    AS Upsert (col1, data)

    ON Upsert.col1 = [@Test].col1

    WHEN MATCHED THEN UPDATE SET data = Upsert.data

    WHEN NOT MATCHED BY TARGET THEN INSERT (col1, data) VALUES (Upsert.col1, Upsert.data);

    SELECT *

    FROM @test-2;

    -- UPSERT a new value

    MERGE @test-2

    USING (

    VALUES (2, 2)

    )

    AS Upsert (col1, data)

    ON Upsert.col1 = [@Test].col1

    WHEN MATCHED THEN UPDATE SET data = Upsert.data

    WHEN NOT MATCHED BY TARGET THEN INSERT (col1, data) VALUES (Upsert.col1, Upsert.data);

    SELECT *

    FROM @test-2;

    Paul

  • Perfect! Solid stuff as usual. Thanks once again Paul!

    I was just checking out MERGE in BOL. BOL used to give version info. in the docs., but I don't see that anymore. Is MERGE available in 2005?. Now that I think about it. This is going to have to run on one of our 2005 servers. Sorry, should have posted in the 2005 forum.

    .

  • MERGE was new for 2008 😛

    Demonstration code using EXISTS:

    -- Test table

    CREATE TABLE #Test

    (col1 INTEGER PRIMARY KEY, data INTEGER NOT NULL);

    -- A row to UPSERT

    INSERT #Test (col1, data) VALUES (1, 1);

    SELECT *

    FROM #Test;

    DECLARE @ID INTEGER,

    @data INTEGER;

    -- A row to UPSERT

    SELECT @ID = 1,

    @data = 100;

    IF EXISTS

    (

    SELECT *

    FROM #Test WITH (UPDLOCK, SERIALIZABLE)

    WHERE #Test.col1 = @ID

    )

    BEGIN

    UPDATE #Test

    SET data = @data

    WHERE col1 = @ID

    END

    ELSE

    BEGIN

    INSERT #Test

    (col1, data)

    VALUES (@ID, @data)

    END;

    SELECT *

    FROM #Test;

    -- Second row to UPSERT

    SELECT @ID = 2,

    @data = 2;

    IF EXISTS

    (

    SELECT *

    FROM #Test WITH (UPDLOCK, SERIALIZABLE)

    WHERE #Test.col1 = @ID

    )

    BEGIN

    UPDATE #Test

    SET data = @data

    WHERE col1 = @ID

    END

    ELSE

    BEGIN

    INSERT #Test

    (col1, data)

    VALUES (@ID, @data)

    END;

    SELECT *

    FROM #Test;

    GO

    DROP TABLE #Test;

    Paul

  • Alex Kuznetsov blogged about this recently, highlighting common code and some considerations to bear in mind. I don't necessarily agree with all the content, but I thought I should reference it before someone else did :laugh:

  • Thanks for the tip on posting code! THAT makes quite a difference!! 😀

    So below is the rough draft. Look good? Still have some defensive coding to do of course. I had no clue lock hints were necessary in this case. Probably saved me a great deal of aggravation! I need to share this with others on the team. I've seen it all over the place in our systems. So without the hints, I'll likely run into deadlocks/concurrency issues right?

    ALTER PROCEDURE SaveAward

    @propCode varchar(10),

    @busDate datetime,

    @localStayId numeric(18, 0),

    @localConfirmationId numeric(18,0),

    @hostConfirmation varchar(20),

    @arrDate datetime,

    @depDate datetime,

    @stayStatus smallint,

    @isNoShow bit,

    @localStayAwardId numeric(18,0),

    @stayDate datetime,

    @awardNum varchar(50),

    @goldPassNum varchar(30)

    AS

    BEGIN

    IF EXISTS(SELECT * FROM AwardsChkOutSummary WITH(UPDLOCK, HOLDLOCK)

    WHERE PropertyCode = @propCode

    AND LocalStayId = @localStayId

    AND StayDate = master.dbo.StripTimeFromDate(@stayDate))

    BEGIN

    UPDATE AwardsChkOutSummary

    SET LocalStayId = @localStayId,

    LocalConfirmation = @localConfirmationId,

    HostConfirmation = @HostConfirmation,

    ArrDate = @ArrDate,

    DepDate = @DepDate,

    StayStatus = @StayStatus,

    IsNoShow = @IsNoShow,

    LocalStayAwardId = @LocalStayAwardId,

    StayDate = @StayDate,

    AwardNum = @AwardNum,

    GoldPassportNum= @GoldPassNum

    WHERE PropertyCode = @propCode

    AND LocalStayId = @LocalStayId

    END

    ELSE

    BEGIN

    INSERT AwardsChkOutSummary(LocalStayId, LocalConfirmation, HostConfirmation, ArrDate,

    DepDate, StayStatus,IsNoShow, LocalStayAwardId, StayDate, AwardNum, GoldPassportNum)

    SELECT @localStayId, @localConfirmationId, @hostConfirmation, @arrDate, @depDate,

    @stayStatus, @isNoShow, @localStayAwardId, @stayDate, @awardNum, @goldPassNum

    END

    END

    .

  • BSavoie (2/21/2010)


    Thanks for the tip on posting code! THAT makes quite a difference!! 😀

    No worries! It's a small thing, but helps a lot, yes.

    BSavoie (2/21/2010)


    So below is the rough draft. Look good? Still have some defensive coding to do of course. I had no clue lock hints were necessary in this case. Probably saved me a great deal of aggravation! I need to share this with others on the team. I've seen it all over the place in our systems. So without the hints, I'll likely run into deadlocks/concurrency issues right?

    Right! 🙂

    In case it's not obvious, the UPDLOCK is there in case we need to update an existing row, and HOLDLOCK (aka SERIALIZBLE) is there to lock the key range in case the row doesn't exist and we need to do an INSERT.

    As far as the code is concerned, it looks pretty good. I'd just make a couple of comments: First, generally the conditions in the EXISTS clause match the WHERE clause in the following UPDATE. I notice that StayDate is in the EXISTS but not there WHERE - just something to check. Normally, the EXISTS check is performed on a PRIMARY or UNIQUE key - is StayDate part of the key?

    Second thing is a very small point: The name of the PROCEDURE is missing a schema-prefix, maybe it should be dbo.SaveAward?

    Paul

  • All good points Paul. Thanks I will address. I did forget StayDate in the Update. "Where" must be the same in both branches. I did create a unique index on the three columns I'm using to find the row. Thanks again, love the feedback. Can't get it at the office.

    .

  • BSavoie (2/21/2010)


    Thanks again, love the feedback. Can't get it at the office.

    :laugh: LOL :laugh:

  • it's my solution

    first step - we try to update record

    secod step - if no record have been updated then we insert record

    update Table

    set field1 = @field

    where field2 = @key

    if @@rowcount=0

    begin

    insert into Table()field1, field2) Values(@field, @key)

    end

    I Have Nine Lives You Have One Only
    THINK!

  • Interesting. Short, sweet, and simple. Avoids the concurrency problems of EXISTS.

    .

Viewing 15 posts - 1 through 15 (of 24 total)

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