I am currently trying to update data of two related tables in one query. While it might not be the best idea because of data redundancy, I want to avoid doing a lot of checks for changed data within my application.
These are the tables in use:
Process (Id, Name, Description)
FavoriteProcess (Id, Process_Id, Position, CreatedTime)
FavoriteProcess
Process
Process
FavoriteProcess
Position
FavoriteProcess
Description
Process
CREATE PROCEDURE UpdateFavoriteProcess
-- parameters for the stored procedure
@Id int,
@ProcessDescription varchar(256),
@Position int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- The update query
UPDATE fp
SET p.Description = @ProcessDescription, -- this line is issuing the error
fp.Position = @Position
FROM FavoriteProcess AS fp
INNER JOIN Process AS p ON fp.Process_Id = p.Id
WHERE fp.Id = @Id
END
GO
Msg 4104, Level 16, State 1, Procedure UpdateFavoriteProcess, Line 13
The multi-part identifier "p.Description" could not be bound.
You cant update two tables at same time
When you start the UPDATE with
UPDATE fp
You cant write
SET p.Description
That is why you get Not Bound
error.