Single Update Query - Required

  • Table 1:

    SnoSIDSname

    1NullA

    2NullB

    3NullC

    4NullD

    5NullE

    Table 2:

    anoaIDaName

    155AA

    232BB

    353CC

    410DD

    510EE

    Requirement:

    I need to update Table 1 - Column SID values with Table 2 - Column aID in a single update query

    Output data should be:

    SnoSIDSname

    155A

    232B

    353C

    410D

    510E

  • This seems quite simple like homework stuff

    Can you let us know what you have tried?

    If you are stuck somewhere, we are happy to help you.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • This should get you started...

    IF OBJECT_ID('tempdb..#table1') IS NOT NULL

    DROP TABLE #table1;

    IF OBJECT_ID('tempdb..#table2') IS NOT NULL

    DROP TABLE #table2;

    CREATE TABLE #table1 (sno int unique NOT NULL, [sid] int NULL, sname varchar(2) unique NOT NULL);

    CREATE TABLE #table2 (ano int unique NOT NULL, [aid] int NULL, aname varchar(2) unique NOT NULL);

    INSERT INTO #table1

    SELECT 1,Null,'A' UNION ALL

    SELECT 2,Null,'B' UNION ALL

    SELECT 3,Null,'C' UNION ALL

    SELECT 4,Null,'D' UNION ALL

    SELECT 5,Null,'E';

    INSERT INTO #table2

    SELECT 1,55,'AA' UNION ALL

    SELECT 2,32,'BB' UNION ALL

    SELECT 3,53,'CC' UNION ALL

    SELECT 4,10,'DD' UNION ALL

    SELECT 5,10,'EE';

    -- Here's what we need #table 1 to look like:

    SELECTt1.sno,

    t2.aid,

    t1.sname

    FROM #table1 t1

    JOIN #table2 t2 ON t1.sno=t2.ano;

    -- This will do it (Update based on a join)

    UPDATE t1

    SET t1.[sid]=t2.aid

    FROM #table1 t1

    JOIN #table2 t2 ON t1.sno=t2.ano;

    -- did it work?

    SELECT * FROM #table1;

    --cleanup

    DROP TABLE #table1;

    DROP TABLE #table2;

    GO

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • A simple way to do this:

    update #Table1

    set #Table1.SID = #Table2.aID

    from #Table2

    where #Table1.Sno = #Table2.Sno ;

  • robertd 77391 (3/12/2013)


    A simple way to do this:

    update #Table1

    set #Table1.SID = #Table2.aID

    from #Table2

    where #Table1.Sno = #Table2.Sno ;

    I can't for the life me remember the details but there is an issue when you do an UPDATE FROM and the table being updated is not in the query. It is better to use the join method as demonstrated by Alan above.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (3/12/2013)


    robertd 77391 (3/12/2013)


    A simple way to do this:

    update #Table1

    set #Table1.SID = #Table2.aID

    from #Table2

    where #Table1.Sno = #Table2.Sno ;

    I can't for the life me remember the details but there is an issue when you do an UPDATE FROM and the table being updated is not in the query. It is better to use the join method as demonstrated by Alan above.

    I think what is needed for this would be this:

    update #Table1

    set SID = (SELECT #Table2.aID from #Table2 where #Table1.Sno = #Table2.Sno) ;

  • Hi All,

    Thanks for your help.

    All your solutions worked!

    Regards,

    Mani

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

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