Update multiple tables using a stored procedure

advertisements

I want use a stored procedure to update multiple tables in a db. Each table has a GUID as the PK and there are FK's between the tables.

For example, one table is "Tool" with a column ID (the guid) and another table is "Type" with ID as guid again. There is a column in Tool called "TYPE_ID" that is a FK to the table Type with Type's Guid stored in it. I want to first update the Tool table and then after, update the Type table based on that FK.

UPDATE Tool
    SET [email protected], [Enabled][email protected], [email protected], [email protected],
        [email protected], [email protected], [email protected]
    WHERE [email protected]

Update Type
    SET [email protected]
    WHERE [email protected] AND
        Tool.TYPE_ID=Type.ID

I know that this code is incorrect for the second update, but this is the gist of how I would like to be able to do it. Is there a way to not have to SELECT the FK Guid, store it, and use it the next update? If that is the only way, how do I do that?


Write your second update like this, joining the Tool table to the Type table:

UPDATE ty
    SET Type = @Type
    FROM Tool to
        INNER JOIN Type ty
            ON to.TYPE_ID = ty.ID
    WHERE to.ID = @ID