Vinzenz Vinzenz - 1 month ago 7
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
FavoriteProcess
entity is related to a
Process
and a
Process
can appear in multiple
FavoriteProcess
entities.

I'm writing a tool with which I can update the
Position
property of a
FavoriteProcess
entity and the
Description
property of the related
Process
as well. For this problem I wrote the following stored procedure which is issuing the error:

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


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!

Answer

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.