Updating joined tables together results in "multi-part identifier could not be bound"

Question

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 1

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.

Updating joined tables together results in "multi-part identifier could not be bound"

Sign up