Update FK Guid from a Select Statement

Lets say I have two tables, Tool and IOPoint, both with GUID as the PK. Tool has a colum开发者_如何学Pythonn that is a GUID FK column to the IOPoint table.

When I change the IOPoint in the program, I don't want to update the value, but instead update the FK from the list of entries in IOPoint (IOPoint table is non-changing).

Therefore, I have to do a SELECT from IOPoint based on 2 unique columns and update THAT row's GUID into the Tool table's FK column.

VAR myGUID = SELECT IOP.ID 
    FROM IOPoint IOP
    WHERE IOP.A = @A AND IOP.B=@B

UPDATE Tool T
    SET T.IOPoint_1 = myGuid
    WHERE T.ID=@ID

I have to do this query about 30 times per db update due to the large number of IOPoints used in the program, so I need to find the most efficient way of doing it that will hopefully not cause performance issues.

Any help is appreciated. Thanks everyone!

I think you can do the SELECT and UPDATE in a single SQL statement a shown below.

Assuming the tables are under dbo schema, the query joins the tables dbo.Tool and dbo.IOPoint using the key column ID and filters the rows by columns A and B. Then, it updates the ID value from table IOPoint to the IOPoint_1 column in table Tool.

I hope that is what you are looking for.

UPDATE      T
SET         T.IOPoint_1 = IOP.ID
FROM        dbo.Tool    T
INNER JOIN  dbo.IOPoint IOP
ON          IOP.ID      = T.ID
WHERE       IOP.A       = @A
AND         IOP.B       = @B