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
  • If you keep your "weird" code, you don't need 2 subqueries: WHERE accountID = @recipientID AND ( SELECT t.CurrencyID FROM table1 t WHERE t.accountID = @payerID ) = table1.CurrencyID ; – ypercubeᵀᴹ Jun 24 '19 at 7:13

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.