Output Clause: The multi-part identifier could not be bound

Question

I am running in to the dreaded "The multi-part identifier could not be bound" error on a stored procedure I am currently working on. I have a few questions in regards to the query below.

1. Why am I getting this error?
2. Why would this error occur on ImportFundingDateTime instead of FloorplanId given that they both come from the same query, but FloorplanId is listed first in the output clause?
3. Can I adjust this query to not get the error while still keeping the general structure the same?

.

DECLARE @Results                Table(
    [FloorPlanId]               UNIQUEIDENTIFIER,
    [ImportFundingDateTime]     DATETIME,
    [TimeStamp]                 VARBINARY(8),
    [BusinessId]                UNIQUEIDENTIFIER
    )

UPDATE CacRecord 
    SET MatchFound = 1
    OUTPUT  fp.[FloorplanId], cr.[ImportFundingDateTime],
            fp.[TimeStamp], buyer.[BusinessId]
    INTO @Results(  [FloorplanId], [ImportFundingDateTime], 
                    [TimeStamp], [BusinessId])
    FROM CacRecord cr WITH (NOLOCK)
    INNER JOIN CacBatch cb WITH (NOLOCK)
        ON cr.CacBatchId = cb.CacBatchId
    INNER JOIN Floorplan fp WITH (NOLOCK)
        ON fp.UnitVIN = cr.ImportVin
        AND COALESCE(fp.UnitVIN, '') <> ''
    INNER JOIN Business buyer WITH (NOLOCK)
        ON buyer.BusinessId = fp.BuyerBusinessId
    LEFT OUTER JOIN BusinessContact bc WITH (NOLOCK)
        ON bc.BusinessId = buyer.BusinessId
    LEFT OUTER JOIN Contact c WITH (NOLOCK)
        ON c.ContactId = bc.ContactId
    WHERE cb.CacJobInstanceId = @cacJobInstanceId
        AND fp.FloorplanStatusId = 1 --Approved
        AND COALESCE(cr.ImportVin, '') <> ''
        AND 1 = 
            CASE
                WHEN cr.ImportFein = buyer.FederalTaxID  
                    AND COALESCE(cr.ImportFein, '') <> '' THEN 1
                WHEN cr.ImportSsn = c.Ssn 
                    AND COALESCE(cr.ImportSsn, '') <> '' THEN 1
                ELSE 0
            END;

Tie

If you run the query using Select fp.[FloorplanId], cr.[ImportFundingDateTime], fp.[TimeStamp], buyer.[BusinessId] and your From and Where clauses, does it execute without error? – Rainey

Answer 1

Please recheck the syntax of the OUTPUT clause OUTPUT on MSDN

Syntax

<column_name> ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }

from_table_name

Is a column prefix that specifies a table included in the FROM clause
of a DELETE or UPDATE statement that is used tospecify the rows to
update or delete.

It looks like you have aliased CacRecord in the FROM clause as "cr", but have not correlated that with the UPDATE clause.

Note: Even with it aliases in the FROM clause and NOT aliased in the UPDATE cause, SQL Server appears to recognize CacRecord as the UPDATE table, requiring you to use INSERTED instead of cr as the virtual table name.

UPDATE cr
SET MatchFound = 1
OUTPUT fp.[FloorplanId], INSERTED.[ImportFundingDateTime],
  fp.[TimeStamp], buyer.[BusinessId]
INTO @Results( [FloorplanId], [ImportFundingDateTime], 
    [TimeStamp], [BusinessId])
FROM CacRecord cr WITH (NOLOCK)
INNER JOIN CacBatch cb WITH (NOLOCK)
 ON cr.CacBatchId = cb.CacBatchId
INNER JOIN Floorplan fp WITH (NOLOCK)
 ON fp.UnitVIN = cr.ImportVin
 AND COALESCE(fp.UnitVIN, '') <> ''
INNER JOIN Business buyer WITH (NOLOCK)
 ON buyer.BusinessId = fp.BuyerBusinessId
LEFT OUTER JOIN BusinessContact bc WITH (NOLOCK)
 ON bc.BusinessId = buyer.BusinessId
LEFT OUTER JOIN Contact c WITH (NOLOCK)
 ON c.ContactId = bc.ContactId
WHERE cb.CacJobInstanceId = @cacJobInstanceId
 AND fp.FloorplanStatusId = 1 --Approved
 AND COALESCE(cr.ImportVin, '') <> ''
 AND 1 = 
  CASE
   WHEN cr.ImportFein = buyer.FederalTaxID  
    AND COALESCE(cr.ImportFein, '') <> '' THEN 1
   WHEN cr.ImportSsn = c.Ssn 
    AND COALESCE(cr.ImportSsn, '') <> '' THEN 1
   ELSE 0
  END;

For visitors to this question, this code block shows multiple tables being referenced in the OUTPUT clause correctly.

create table TO1 (id int, a int);
create table TO2 (id int, b int);
create table TO3 (id int, c int);
insert into TO1 select 1,1;
insert into TO2 select 1,2;
insert into TO3 select 1,3;
insert into TO3 select 1,4;

declare @catch table (a int, b int, c int)
update c
set c = a.a
output a.a, b.b, INSERTED.c
into @catch(a,b,c)
from TO1 a
inner join TO2 b on a.id=b.id
inner join TO3 c on a.id=c.id

Ilse

The fields definitely exist in the tables referenced. I have validated the spelling on them. – Tie

@Phillip, I have clarified further in the answer. Use INSERTED instead of c because that table itself is being updated. – Ilse