Post reply

If Exist, Update Value in Table

  • May 15, 2009 at 9:40 am

    #384599

    I have a need to update a reference table of values from a temporary table.

    The reference table (tblFileNumber) has two fields:

    FileNumberID (PK)

    FileNumber (varchar)

    The temporary table (tblUpdates) has 22 columns, one of which is FileNumberRF. Before truncating the data in the tblUpdates table, I need to search the tblFileNumber to see if each FileNumberRF value is already listed in the tblFileNumber table. If not, I need to add it.

    I've read up on If Exist statements. However, I am not sure how to put it together in this case. Any suggestions would be welcome. Thanks.

  • May 15, 2009 at 9:48 am

    #995465

    Sounds fairly simple, if I understand everything. There are a couple of syntaxes you could use to do it, but. in my opinion, the easiest to understand is this one:

    -- i am assuming that teh FileNumberID is an Identity() Column

    INSERT INTO tblFileNumber

    (

    FileNumber

    )

    SELECT

    TU.FileNumberRF

    FROM

    /*

    Left Outer Join says give me all rows in the

    first table regardless of if there is a match

    in the second table.

    */

    tblUpdates AS TU LEFT OUTER JOIN

    tblFileNumber AS TFN ON

    TU.FileNumberRF = TFN.FileNumber

    WHERE

    -- eliminates rows with a match

    TFN.FileNumber IS NULL

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • May 15, 2009 at 9:50 am

    #995467

    Damn jack, you beat me to it, 😉 Good job!

    Cheers,

    J-F

  • May 15, 2009 at 10:10 am

    #995490

    Or using EXISTS as was mentioned.

    INSERT INTO tblFileNumber

    SELECT TU.FileNumberRF

    FROM tblUpdates AS TU

    WHERE NOT EXISTS (SELECT * FROM tblFileNumber AS TFN

    WHERE TFN.FileNumber= TU.FileNumberRF )

  • May 15, 2009 at 10:21 am

    #995498

    J-F,

    I was wondering how close we came.

    Tom,

    Yeah that is a good solution as well, I just think for newer developers the LEFT JOIN syntax is easier to understand. I actually use the EXISTS/NOT EXISTS more often than the LEFT JOIN syntax in my own development now.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • May 18, 2009 at 11:20 am

    #996515

    I got the Insert into suggestion working without a problem. Question: something I should have mentioned is that there are a couple of million FileNumbers in the reference table (indexed of course) and each day it is possible to add a few thousand more File Numbers to the reference list. In terms of performance, would the “if exist” statement work better or just about the same as using the insert into?

  • May 18, 2009 at 12:20 pm

    #996553

    You would need to test that situation. It appears that there can be a performance benefit by using EXISTS/NOT EXISTS, but you should test both before you make a final decision.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

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

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