One of the most common tasks in SQL Server is updating data in a table based on values from another table. However, not everyone is aware of the JOIN clause in the UPDATE statement, which can simplify this process significantly.

Let’s consider an example where we have two tables, Table1 and Table2:

-- Create table1
CREATE TABLE Table1 (
    Col1 INT,
    Col2 INT,
    Col3 VARCHAR(100)
)

INSERT INTO Table1 (Col1, Col2, Col3)
VALUES
    (1, 11, 'First'),
    (11, 12, 'Second'),
    (21, 13, 'Third'),
    (31, 14, 'Fourth')

-- Create table2
CREATE TABLE Table2 (
    Col1 INT,
    Col2 INT,
    Col3 VARCHAR(100)
)

INSERT INTO Table2 (Col1, Col2, Col3)
VALUES
    (1, 21, 'Two-One'),
    (11, 22, 'Two-Two'),
    (21, 23, 'Two-Three'),
    (31, 24, 'Two-Four')

Now, let’s say we want to update the values of Col2 and Col3 in Table1 based on the values in Table2, specifically for the rows where Col1 is 21 and 31.

Instead of using complex solutions like cursors or table variables, we can simply use the JOIN clause in the UPDATE statement to achieve this:

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)

By using the JOIN clause, we can easily update data in one table from another table. This approach is not only simpler but also more efficient compared to other methods like using the MERGE statement.

Let’s check the updated data in the tables:

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

As you can see, the values in Table1 have been updated based on the values in Table2.

Finally, let’s clean up by dropping the tables we created:

DROP TABLE Table1
DROP TABLE Table2

Using the JOIN clause in the UPDATE statement is a powerful technique that can simplify the process of updating data in SQL Server. If you have any other tricks or methods for similar situations, feel free to share them with me.