temporary table | multi-part identifier could not be bound

Question

Every other article I see has something to with JOINS... I'm not even trying to do a join! I'm just trying to run a simple UPDATE based off information in a temporary table. Here's the code...

BEGIN TRAN ArchiveMigration

-- insert into temporary table
CREATE TABLE #tblTemp(
    [theID] [int] NOT NULL,
    [ScheduleID] [int] NOT NULL,
    [OverridingCustomerID] [int] NOT NULL,
    [Timestamp] [datetime] NOT NULL,
    [DeviceName] [nvarchar](max) NULL,
    [DestinationTempCool] [int] NULL,
    [DestinationMode] [nvarchar](max) NULL,
    [DestinationTempHeat] [int] NULL,
CONSTRAINT [PK_#tblTemp] PRIMARY KEY CLUSTERED 
(
[theID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO #tblTemp ([theID], [ScheduleID], [OverridingCustomerID], Timestamp, DeviceName, DestinationTempCool, DestinationMode, DestinationTempHeat)
SELECT Id, ScheduleId, OverridingCustomerId, Timestamp, DeviceName, DestinationTempCool, DestinationMode, DestinationTempHeat
FROM CustomerScheduleOverride
WHERE Id = 836;

-- modify the extended info table
UPDATE ExtendedOverrideInfo
SET ExtendedOVerrideInfo.OverrideId = Null
WHERE ExtendedOverrideInfo.OverrideId = #tblTemp.[theID];

COMMIT TRAN

All I want to do is nullify the values of ExtendedOverrideInfo.OverrideId if said ID exists in the #tblTemp (statement is towards the bottom of the script). Any idea why I might be getting this message? Thanks in advanced!

Possible duplicate of [Update table based on another table](http://stackoverflow.com/questions/6023367/update-table-based-on-another-table) — Tab Alleman, Feb 24 '16 at 20:19

score 4 · Answer 1 · answered Feb 24 '16 at 20:15

4

Your current UPDATE syntax is incorrect, you will need to use a JOIN on your temporary table. This article from Pinal Dave provides a more detailed explanation.

UPDATE ExtendedOverrideInfo 
SET ExtendedOverrideInfo.OverrideId = Null
FROM ExtendedOverrideInfo
INNER JOIN #tblTemp t on t.[theID]=ExtendedOverrideInfo.OverrideId

answered Feb 24 '16 at 20:15

Alex

21,273
10
61
73

Thanks! I'm a bit confused as to the purpose of using a JOIN there... I guess I'll need to do a bit of research. – Malprodigy Feb 24 '16 at 22:18
Thanks a lot for the help. Now i'm facing an issue where rather than nulling, I need to set ExtendedOverrideInfo.ArchiveId = OverrideArchive.archiveID, but i'm getting that multi-part identifier error again... any idea how I could fix this? – Malprodigy Feb 26 '16 at 17:57

score 0 · Answer 2 · answered Feb 24 '16 at 23:04

You update statment is totally wrong,the where clause is not correct,you have multiple choices here to resolve your problem:

make join with tmptable
use Exists key in your where clause
Or simply,if your purpose of creating tmptable is just to nullify,why not using cursor?or change your where statment to search record by id?

temporary table | multi-part identifier could not be bound

2 Answers2