SQL Question
Updating joined tables together results in "multi-part identifier could not be bound"
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)
As you see every
FavoriteProcessProcessProcessFavoriteProcessI'm writing a tool with which I can update the
PositionFavoriteProcessDescriptionProcessCREATE 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
The query is issuing this error:
Msg 4104, Level 16, State 1, Procedure UpdateFavoriteProcess, Line 13
The multi-part identifier "p.Description" could not be bound.
I've tried searching for this problem but none of the 5 related SO questions provided a solution that could solve my problem! I tried...
- ...surrounding all tables and columns with []
- ...putting brackets () in any possible way around the join statement
but none of my attempts succeeded. So I'm really thankful for someone who can help me with this problem!
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
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.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download