Post reply

need to update 2nd table if data exists in table 1

  • February 3, 2014 at 3:48 am

    #303677

    Hi everyone, I've 2 tables and both have a common field. What I'm after is to search table 1 and if the data exists in table 2 then update a seperate field also in table 2. I'm assuming some kind of join would be used, but I'm not sure how. I've written a script to create both tables, the idea is if the data in postcode in table 1 is the same as postcode in table 2 then update the field found in table 2 with 'ok'. I hope my question makes sence

    CREATE TABLE Table1(postcode VARCHAR(20), name VARCHAR(20))

    GO

    -- Inserting Data into Table

    INSERT INTO Table1(postcode,name)

    VALUES('df1 6rd','tony')

    INSERT INTO Table1(postcode,name)

    VALUES('sm1 2sq','George')

    INSERT INTO Table1(postcode,name)

    VALUES('ad4 2we','john')

    INSERT INTO Table1(postcode,name)

    VALUES('gy1 1we','mary')

    INSERT INTO Table1(postcode,name)

    VALUES('df1 6rd','peter')

    CREATE TABLE Table2(postcode VARCHAR(20),found varchar(10))

    GO

    -- Inserting Data into Table

    INSERT INTO Table2(postcode)

    VALUES('fd1 6xd')

    INSERT INTO Table2(postcode)

    VALUES('hg5 6rd')

    INSERT INTO Table2(postcode)

    VALUES('df1 8rd')

    INSERT INTO Table2(postcode)

    VALUES('df2 1qd')

    INSERT INTO Table2(postcode)

    VALUES('ad4 2we')

    INSERT INTO Table2(postcode)

    VALUES('sm1 2sq')

  • February 3, 2014 at 4:01 am

    #1685424

    Would something like the below do the trick

    UPDATE dbo.Table2

    SET found = 'OK'

    FROM dbo.Table2 t2

    INNER JOIN dbo.Table1 t1 ON t2.postcode = t1.postcode

  • February 3, 2014 at 4:07 am

    #1685425

    That is excellant, thank you so much Michael

  • February 4, 2014 at 9:07 am

    #1685906

    I'll leave 2 other options, just for the fun.

    UPDATE t2

    SET found = 'OK'

    FROM #Table2 t2

    WHERE EXISTS( SELECT 1 FROM #Table1 t1 WHERE t2.postcode = t1.postcode)

    UPDATE t2

    SET found = 'OK'

    FROM #Table2 t2

    WHERE t2.postcode IN ( SELECT t1.postcode FROM #Table1 t1)

    And a reference: http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • February 7, 2014 at 1:32 am

    #1686927

    Many thanks Luis, all helps with my learning of SQL

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

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