Where's the bug in this simple UPDATE FROM query?

Question

This should be very simple, but I want to get this right because I'm making a change to a live database (it is backed up).

I imported a few column values from CSV file to a database table.

Now I just want to update a target table (T in query) from the imported source table (S in the query).

I get the error: The multi-part identifier "T.account_manager_id" could not be bound.

update DADatabaseMarch2012.dbo.Campaign

set 
    T.[account_manager_id]=S.[account_manager_id]
   ,T.[campaign_status_id]=S.[campaign_status_id]
   ,T.[ad_manager_id]=S.[ad_manager_id]
   ,T.[advertiser_id]=S.[advertiser_id]
from 
    [zMarch12CampaignsRestore].[dbo].[March12Eom_Campaigns] S
    inner join DADatabaseMarch2012.dbo.Campaign T ON S.id = T.id

possible duplicate of [How to use a table variable in an "update from select" query?](http://stackoverflow.com/questions/8535362/how-to-use-a-table-variable-in-an-update-from-select-query) — JonH, Apr 12 '12 at 19:50
Not the same query but the same type of issue. Use the alias name in the update statement. — JonH, Apr 12 '12 at 19:51

score 6 · Accepted Answer · answered Apr 12 '12 at 19:48

6

Write it this way, using the alias name as the target of the UPDATE.

update T

set 
    [account_manager_id]=S.[account_manager_id]
   ,[campaign_status_id]=S.[campaign_status_id]
   ,[ad_manager_id]=S.[ad_manager_id]
   ,[advertiser_id]=S.[advertiser_id]
from 
    [zMarch12CampaignsRestore].[dbo].[March12Eom_Campaigns] S
    inner join DADatabaseMarch2012.dbo.Campaign T ON S.id = T.id

answered Apr 12 '12 at 19:48

Joe Stefanelli

132,803
19
237
235

Does this really work? I think the update clause needs to reference exactly the same table as the from clause. See my answer. – usr Apr 12 '12 at 19:51
@usr yes it works. If you alias a table in an `UPDATE` you must specify the **alias** as the target – JNK Apr 12 '12 at 19:54
I know but you don't use that table in the from. You use it in a join. Surprising that this is working. – usr Apr 12 '12 at 19:56
no, I do use the table that T is an alias for in the join.... maybe one of us is confused (i mean that in an 'it could be me' way)? – Aaron Anodide Apr 12 '12 at 20:01
@usr it's an inner join so it doesn't matter – JNK Apr 12 '12 at 20:20

score 4 · Answer 2 · answered Apr 12 '12 at 19:49

4

Since T is the target table, you don't have to alias it in the left hand side of the assignments (the SETs).

answered Apr 12 '12 at 19:49

Ann L.

13,760
5
35
66

score 1 · Answer 3 · edited Apr 12 '12 at 20:04

1

Edit: You need to reference the same table in your update as in you from clause. Write "update T".

edited Apr 12 '12 at 20:04

Aaron Anodide

16,906
15
62
121

answered Apr 12 '12 at 19:48

usr

168,620
35
240
369

Where's the bug in this simple UPDATE FROM query?

3 Answers3