Do UPDATE in SQL using a JOIN

Question

Hi

I am using SQL Server 2008, and trying to run the following query in management studio:

        UPDATE
        Table1 as T1 INNER JOIN Table2 as T2 ON T1.ID=T2.ID
        SET T1.SomeValue = T2.SomeValue

        GO

Doesn't work though. Is this sort of thing supported?

Karl

[EDIT] Let me just be more clear, I wan't to do something like:

        UPDATE
        Table1 as T1 INNER JOIN Table2 as T2 ON T1.ID=T2.ID
        SET T1.SomeValue = T2.SomeValue
        T1.SomeValue2 = T2.SomeValue2
        T1.SomeValue3 = T2.SomeValue3
        T1.SomeValue4 = T2.SomeValue4

        GO

i.e. without having to explicitly do T1.SomeValue = SELECT..., T2.SomeValue = SELECT...

Answer 1

It should work if you rewrite it similar to this:

UPDATE Table1 
SET Table1.SomeValue = T2.SomeValue
FROM Table2 AS T2 
WHERE Table1.ID = T2.ID

Answer 2

Try this

DECLARE @Table1 TABLE(
     ID INT,
     Val VARCHAR(MAX)
)

DECLARE @Table2 TABLE(
     ID INT,
     Val VARCHAR(MAX)
)

INSERT INTO @Table1 (ID,Val) SELECT 1, ''
INSERT INTO @Table1 (ID,Val) SELECT 2, ''
INSERT INTO @Table1 (ID,Val) SELECT 3, ''

INSERT INTO @Table2 (ID,Val) SELECT 1, 'a'
INSERT INTO @Table2 (ID,Val) SELECT 2, 'a'

UPDATE @Table1
SET Val = t2.Val
FROM @Table1 t1 INNER JOIN
     @Table2 t2 ON t1.ID = t2.ID


SELECT * FROM @Table1

Answer 3

I think that

UPDATE Table1 as T1...

would result in the alias being updated and the actual table remaining unchanged.

You could use a scalar subquery e.g.

UPDATE Table1 
   SET SomeValue = (
                    SELECT T2.SomeValue
                      FROM Table2 as T2 
                     WHERE T2.ID = Table1.ID
                   );

Answer 4

Update T1 set T1.Col1 = T2.Col2 Inner Join T2 on T1.Id = T2.FId

Answer 5

Avoid using the "UPDATE with a join" feature in SQL Server 2008. Use MERGE instead. MERGE is Standard SQL (unlike the UPDATE join syntax), frequently performs better and is more reliable because it doesn't suffer the same flaws as Microsoft's proprietary UPDATE syntax.