You may be familiar with SQL Server error 4104 that reads something like “The multi-part identifier “DELETED.Name” could not be bound“, which tells us that the “multi-part identifier” couldn’t be bound.

You may have seen this error when performing joins across tables when using the wrong table prefix for a column or using the table name instead of its alias. But that’s not the only place we can get this error.

If you’re getting this error while using the OUTPUT clause (which we can use during INSERT, UPDATE, DELETE, or MERGE operations), then it could be that you’ve accidentally used the wrong prefix for the affected column/s.

Example of Error

Here’s an example of code that produces the error:

INSERT INTO Products (Name, Price)
OUTPUT 
    INSERTED.ProductID, 
    DELETED.Name
VALUES 
    ('Product A', 9.99), 
    ('Product B', 19.99);

Output:

Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "DELETED.Name" could not be bound.

We got the “multi-part identifier … could not be bound” error.

The reason for this error is because I used the wrong prefix for the column name in the OUTPUT clause. Specifically, I used the DELETED prefix when I should have used the INSERTED prefix. We can see that the first column is correctly prefixed, but not the second column.

We can get the same error when doing a DELETE operation, except the other way around. We’ll get an error if we specify INSERTED while doing a DELETE.

Solution

By now I’m sure you know the solution to this problem; change the prefix to the correct one for the operation.

So in our example, we need to change DELETED to INSERTED:

INSERT INTO Products (Name, Price)
OUTPUT 
    INSERTED.ProductID, 
    INSERTED.Name
VALUES 
    ('Product A', 9.99), 
    ('Product B', 19.99);

Output:

ProductID  Name       Price
---------  ---------  -----
1          Product A  9.99 
2          Product B  19.99

This time both columns are correctly prefixed with INSERTED and the operation ran as expected.

As mentioned, if you’re doing a DELETE, then you’d need to make sure the prefix is DELETED.

If you’re doing an UPDATE, then it could be either, depending on whether you want to get the old value or the newly updated value (DELETED for the old value, INSERTED for the new value).