Multiple Selects to update tblItems – INNER JOIN required?

November 28, 2023

I need to update the buyersAccountID in tblITEMS with the accountID of a winner who’s boxNo in tblEntrants matches ‘winner’ in tblItems:

TBLENTRANTS
fileID
boxNo
accountID

TBLITEMS
fileID
buyersAccountID
winner

So to clarify: If ‘winner’ which is an integer in tblItems matches the integer boxNo in tblEntrants then I need to store tblEntrants.accountID in tblItems.buyersAccountID

fileID also needs to be matched in each table.

UPDATE tblItems SET buyersAccountID = SELECT tblEntrants.accountID WHERE tblEntrants.boxNo = tblItems.winner) WHERE fileID=@fileID

>Solution :

With so little information, this may the script you need:

UPDATE I
SET I.buyersAccountID = E.accountID
FROM tblItems AS I
INNER JOIN tblEntrants AS E ON I.fileID = E.fileID
AND I.winner = E.boxNo;

You can add your where after this query (before the semicolon). For example

UPDATE I
SET I.buyersAccountID = E.accountID
FROM tblItems AS I
INNER JOIN tblEntrants AS E ON I.fileID = E.fileID AND I.winner = E.boxNo
WHERE I.fileID=@fileID;