Sql-server – Write update in more efficient way with joins

join;sql servert-sqlupdate

How can I write this update with joins in 'more sql' way? I have been told that this is weird coding:

-- Updates recipient's account
    UPDATE table1
    SET balance = balance + @amount
    WHERE accountID = @recipientID
        AND -- check if a currency is the same in both accounts
        (
            SELECT t.CurrencyID
            FROM table1 t
            WHERE t.accountID = @payerID
            ) = (
            SELECT t.CurrencyID
            FROM table1
            WHERE t.accountID = @recipientID
            );

Best Answer

You can use an INNER JOIN on CurrencyID to join table1 back on itself but filter the table being updated to records where accountID = @recipientID and the joined table is filtered where accountID = @payerID

In this fashion, only record meeting the WHERE clause for both tables that also matches CurrencyID values will be included in the UPDATE.

UPDATE t1
SET t1.balance = t1.balance + @amount
FROM table1 t1
INNER JOIN table1 t2 ON t1.CurrencyID = t2.CurrencyID
WHERE t1.accountID = @recipientID
    AND t2.accountID = @payerID
Related Question