sqlsql-serversql-server-2008-r2

Updating and join on multiple rows, which row's value is used?


Let's say I have the following statement and the inner join results in 3 rows where a.Id = b.Id, but each of the 3 rows have different b.Value's. Since only one row from tableA is being updated, which of the 3 values is used in the update?

UPDATE a
SET a.Value = b.Value
FROM tableA AS a
INNER JOIN tableB as b 
ON a.Id = b.Id

Solution

  • I don't think there are rules for this case and you cannot depend on a particular outcome.

    If you're after a specific row, say the latest one, you can use apply, like:

    UPDATE  a
    SET     a.Value = b.Value
    FROM    tableA AS a
    CROSS APPLY
            (
            select  top 1 *
            from    tableB as b
            where   b.id = a.id
            order by
                    DateColumn desc
            ) as b