SQL SERVER – UPDATE From SELECT Statement – Using JOIN in UPDATE Statement – Multiple Tables in Update Statement

This is one of the most interesting questions I keep on getting on this email and I find that not everyone knows about it. In recent times I have seen a developer writing a cursor to update a table. When asked the reason was he had no idea how to use multiple tables with the help of the JOIN clause in the UPDATE statement.

Let us see the following example. We have two tables Table 1 and Table 2.

-- Create table1
CREATE TABLE Table1 (Col1 INT, Col2 INT, Col3 VARCHAR(100))
INSERT INTO Table1 (Col1, Col2, Col3)
SELECT 1, 11, 'First'
UNION ALL
SELECT 11, 12, 'Second'
UNION ALL
SELECT 21, 13, 'Third'
UNION ALL
SELECT 31, 14, 'Fourth'
GO
-- Create table2
CREATE TABLE Table2 (Col1 INT, Col2 INT, Col3 VARCHAR(100))
INSERT INTO Table2 (Col1, Col2, Col3)
SELECT 1, 21, 'Two-One'
UNION ALL
SELECT 11, 22, 'Two-Two'
UNION ALL
SELECT 21, 23, 'Two-Three'
UNION ALL
SELECT 31, 24, 'Two-Four'
GO

Now let us check the content in the table.

SELECT *
FROM Table1
SELECT *
FROM Table2
GO

Now let us see the following image. Our requirement is that we have Table2 which has two rows where Col1 is 21 and 31. We want to update the value from Table2 to Table1 for the rows where Col1 is 21 and 31. Additionally, we want to update the values of Col2 and Col3 only.

When you look at this it looks very simple but when we try to think the solution, I have seen developers coming up with many different solutions for example sometime they write cursor, table variables, local variables etc. However, the easiest and the most clean way is to use JOIN clause in the UPDATE statement and use multiple tables in the UPDATE statement and do the task.

UPDATE Table1
SET Col2 = t2.Col2,
Col3 = t2.Col3
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
WHERE t1.Col1 IN (21, 31)
GO

Now let us select the data from these tables.

-- Check the content of the table
SELECT *
FROM Table1
SELECT *
FROM Table2
GO

As you can see that using JOIN clause in UPDATE statement it makes it very easy to update data in one table from another table. You can additionally use MERGE statement to do the same as well, however I personally prefer this method. Let us clean up the clause by dropping the tables which we have created.

DROP TABLE Table1
DROP TABLE Table2
GO

Do let me know if you use any other trick in similar situations. If you do, I would like to learn more about it.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

33 thoughts on “SQL SERVER – UPDATE From SELECT Statement – Using JOIN in UPDATE Statement – Multiple Tables in Update Statement

  1. I use this trick often. A couple of others I use.
    – Update with replace. Eg. UPDATE Table SET Col1 = REPLACE(Col1, ‘.’, ‘ ‘)
    – If migrating data from one table (or database) to another I will use insert with select, this can allow for data manipulation also. I will often use the SSMS Script As options to get the insert statement and then write the select to return the correct values to be inserted. This works for existing tables, whilst Select Into does not.

    Brad

  2. Nice post, Pinal, as always. For rookies, of course :)
    I recommend to everybody to read Izik Ben-Gan’s “T-SQL Fundamentals” to know such a basic stuff like JOINs in DML, MERGE, table expressions, subqueries, isolation lvls and blocking, and algebra fundamentals of SQL-language. This will save Pinal’s time for real )))

    I only wonder, why you’re not using INSERT VALUES instead of INSERT SELECT UNION ALL – you can really make use of this tiny liitle feature called “Table Value Constructor” and define virtual table expressions on the basis if VALUES. Anyway, SELECT UNION ALL still works out for anything below SQL Server 2008.

      • Reasonable )) And another reason I guess is to bypass the annoying “1000 rows per insert with VALUES” rule )))

      • update f set
        did = f.id,
        name = f.ids
        from
        (
        select t.id tid, t.name, d.id did, d.ids
        from @t t left outer join @d d on t.id = d.id
        )f

        Is it possible to update two table column at single query ?

  3. Pingback: SQL SERVER – DELETE From SELECT Statement – Using JOIN in DELETE Statement – Multiple Tables in DELETE Statement | SQL Server Journey with SQL Authority

  4. Hi,
    very nice article, as always.

    Just little note…I prefer to use table name “t1″ instead “Table1″ in “UPDATE” statement:

    UPDATE t1 — instead Table1
    SET Col2 = t2.Col2,
    Col3 = t2.Col3
    FROM Table1 t1
    INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
    WHERE t1.Col1 IN (21, 31)

    Reason: Some time Table1 joined to itself by some criteria and “update t1″ resolves ambiguous names

  5. Hi Folks

    My Question:I need to update different columns in different table.I have around 500+ table .
    I have specific character that needs to be replaced with ”

    In each table I have to update a specific column.I have that list with me .

    Approach:I thought of creating arrays for tab n col

    Check the code below n help me with this

    CREATE Or REPLACE TYPE TABARRAY AS VARRAY(3) OF VARCHAR2(10);
    CREATE Or REPLACE TYPE COLARRAY AS VARRAY(3) OF VARCHAR2(10);
    CREATE Or REPLACE TYPE ILLARRAY AS VARRAY(3) OF VARCHAR2(10);

    DECLARE
    type TABARRAY IS VARRAY(5) OF VARCHAR2(10);
    TABNAMES TABARRAY;
    type COLARRAY IS VARRAY(5) OF VARCHAR2(10);
    COLNAMES COLARRAY;
    type ILLARRAY IS VARRAY(5) OF VARCHAR2(10);
    ILLNAMES ILLARRAY;
    TOTAL INTEGER;

    BEGIN
    TABNAMES :=TABARRAY(‘siebel.S_ORDER’,’siebel.S_SRV_REQ’);
    COLNAMES :=COLARRAY(‘X_SPECIAL_SERVICE_CD’,’X_SGSTNS_PREV’);
    ILLNAMES :=ILLARRAY(‘HE’,’EA’);
    TOTAL :=TABNAMES.COUNT;
    ILLTOTAL :=ILLNAMES.COUNT;
    FOR I IN TOTAL
    LOOP
    FOR J IN ILLTOTAL
    LOOP
    UPDATE TABNAMES(I) SET COLNAMES(I)=REPLACE(COLNAMES(I),ILLNAMES(J),”);
    END LOOP;
    END LOOP;
    END;

  6. How can i update different Table Column ????
    I m Trying Like below

    UPDATE Customer
    SET t1.CustName=’John’,
    t2.Amount=’123654′
    FROM Customer t1
    INNER JOIN Payment t2 ON t1.CID = t2.CID
    WHERE t1.CID IN (1002)

    here i am Getting an Error

    Error: The multi-part identifier “t1.CustName” could not be bound.
    Help me in this soon as possible

    My mail ID [email removed]

    REGARDS

    ASIM

  7. I have an issue with how SQL behaves when doing these updates in the following scenarios.

    Lets say one day someone comes along and adds 1 row to Table 2 like this:

    Col1 Col2 Col3
    21 33 Three-Three

    SQL server will randomly pick one of the rows where Col1=21 from table 2 and update to table 1 for you. Good thing is the statement won’t cause a failure. Bad thing is that no one will know about it until a business user calls you and asks why they get a diff value everyday.

  8. Really a novice question here. I am Using MSSQL 2012. I created a new request using all of your example statements. It worked perfectly. My question is where do the are the apparent alias names of t1 and t2 come frome? In the querysql window, If I hover over the “t2″ in the line
    SET Col2 = t2.Col2,
    the server code knows this is “fullyqualifiedpath”Table2 AS t2 and similar for “t1″
    Is this really an alias or is it something else and how are the name t1 and t2 derived?

    Thanks

  9. Hi,
    It’s really helpful for me.
    I was change the statement like below, and got the same result, my question is how different in these two statements ?

    UPDATE Table1
    SET Table1.Col2 = Table2.Col2,
    Table1.Col3 = Table2.Col3
    FROM Table2
    WHERE Table1.Col1 = Table2.Col1 AND Table1.Col1 IN (21, 31)
    GO

  10. Hi,
    It’s really helpful for me.
    I was change the statement like below, and got the same result, my question is what’s different in these two statements ?

    UPDATE Table1
    SET Table1.Col2 = Table2.Col2,
    Table1.Col3 = Table2.Col3
    FROM Table2
    WHERE Table1.Col1 = Table2.Col1 AND Table1.Col1 IN (21, 31)
    GO

  11. performance wise I liked; (not my! code).. guess you already covered it somewhere..

    ;WITH CTE
    AS (SELECT T1.Col1,
    T2.Col1 AS _Col1,
    T1.Col2,
    T2.Col2 AS _Col2
    FROM T1
    JOIN T2
    ON T1.id = T2.id
    /*Where clause added to exclude rows that are the same in both tables
    Handles NULL values correctly*/
    WHERE EXISTS(SELECT T1.Col1,
    T1.Col2
    EXCEPT
    SELECT T2.Col1,
    T2.Col2))
    UPDATE CTE
    SET Col1 = _Col1,
    Col2 = _Col2

    from stackoverflow.. http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server

  12. Oh sorry, it does work on linked server, my mistake. Just as fast/slow as where column in (select…) which was a possiblity for me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s