January 13, 2010 at 2:23 pm
I have a question about some code that I wrote. I don’t understand why I got an error message and figure I’m missing something (obviously), but have no idea where to start to find out what I’m missing.
I was doing an update from one table (main2) to another table (main). I had taken a portion of the data from the table main and done a SELECT * INTO main2. What I needed to do was assign an orderid to the records in the table and I wanted to do that in a separate table so that if I goofed, it was easy to repair.
Everything was fine until I started to update the main table.
I wrote this SELECT statement to make sure I was matching things up before I executed.
select m.listid, m2, listid, m.orderid, m2.orderid
from dbo.main m
inner join dbo.main m2
on m.listid = m2.listid
This showed me that I was matching the main.listid to main2.listid and that the main.orderid was null (that’s the field I’m updating) and that main2.orderid had been assigned or was null.
So, the next thing I do is my update statement, just replacing the SELECT with an UPDATE <table> SET <field1> = <field2>.
update dbo.main
set m.orderid = m2.orderid
from dbo.main m
inner join dbo.main2 m2
on m.listid = m2.listid
and I get this error message:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "u.orderid" could not be bound.
When I change the first instance of m.orderid to main.orderid, the update works just fine.
update dbo.main --This update works
set main.orderid = m2.orderid
from dbo.main m
inner join dbo.main2 m2
on m.listid = m2.listid
Why did the first one not work? I’m assuming it has something to do with the alias assigned and when SQL gets to figuring out the alias, but can someone point me to a reference that says why I’m getting this behavior?
-- Kit
January 13, 2010 at 4:08 pm
You may be able to alias the table name in the update statement in Oracle but
The table name in the UPDATE can't be aliased in T-SQL or in Standard SQL.
January 13, 2010 at 4:16 pm
update m
set m.orderid = m2.orderid
from dbo.main m
inner join dbo.main2 m2
on m.listid = m2.listid
January 15, 2010 at 4:10 am
jcdyntek (1/13/2010)
You may be able to alias the table name in the update statement in Oracle butThe table name in the UPDATE can't be aliased in T-SQL or in Standard SQL.
Try this on SQL Server 2005
Use AdventureWorks
UPDATE a
SET [City] = 'TestIt'
FROM Person.Address a
where AddressID = 1
--Put it back to what it was
UPDATE a
SET [City] = 'Bothell'
FROM Person.Address a
where AddressID = 1
January 15, 2010 at 4:52 am
Hello
The UPDATE and the SET apply to the same table.
Ideally the first table listed after FROM is also the target of the update.
In your case, you've aliased the target of the update in the FROM clause, but not used the alias in the UPDATE or SET
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 18, 2010 at 8:19 am
Chris Morris-439714 (1/15/2010)
HelloThe UPDATE and the SET apply to the same table.
Ideally the first table listed after FROM is also the target of the update.
In your case, you've aliased the target of the update in the FROM clause, but not used the alias in the UPDATE or SET
Ah. Thanks for the info. Thought the table name after the UPDATE needed to be the full table name and not the alias. That makes sense now. BOL isn't exactly clear on that in their description of UPDATE.
-- Kit
January 18, 2010 at 8:24 am
Kit G (1/18/2010)
Chris Morris-439714 (1/15/2010)
HelloThe UPDATE and the SET apply to the same table.
Ideally the first table listed after FROM is also the target of the update.
In your case, you've aliased the target of the update in the FROM clause, but not used the alias in the UPDATE or SET
Ah. Thanks for the info. Thought the table name after the UPDATE needed to be the full table name and not the alias. That makes sense now. BOL isn't exactly clear on that in their description of UPDATE.
No worries Kit. Other far wiser than I figured this out and backed it up with testing long ago.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply