|
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
Update Set clause using aliased table incorrect column list |
|
5.1.10 Pro
SSMS 2008
SQL 2000
|
|
Update t
Set
From dbo.Table2 t2
Inner join dbo.Table1 t
On t2.ID = t.ID |
Pulling up the column list on the set clause uses the columns from Table2 instead of Table1. If Table1 was the first table, it works properly.
(I may be reporting this one again. If so, sorry.)
|
|
Wed Sep 22, 2010 9:13 am |
|
|
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7907
|
|
|
|
This doesn't work as you expect. The behavior for T-SQL specific syntax for UPDATE alias SET... FROM table list/joins is the same as for SELECT ... FROM table list/joins. "FROM" clause is driving the column prompt, not the UPDATE. That's why the columns are listed in the order or table references. We have a queued enhancement in the pipeline to implement recognition and handling of T-SQL specific UPDATE … FROM statements.
|
|
Wed Sep 22, 2010 10:11 am |
|
|
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
|
|
One related item. If you have an update where the table you are updating is the first table specified and the table is aliased:
|
|
Update t
Set
From dbo.Table1 t
Inner join dbo.Table2 t2
On t1.ID = t2.ID |
each of the columns on the left side of the assignement in the set clause is aliased:
|
|
Set t.Field1 = t2.Field1 |
It should be
|
|
Set Field1 = t2.Field1 |
Another item, in the preceding example, if you select t2.Field1, it ends up:
|
|
Set Field1 = t2.Field1 = ? |
should be
|
|
Set Field1 = t2.Field1 |
|
|
Wed Sep 29, 2010 11:33 am |
|
|
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7907
|
|
|
|
Thank you for your suggestions.
All of that does require indeed an SQL intellisene method specific to UPDATE...FROM syntax. I'm going to copy your suggestions to the queue enhacment.
|
|
Wed Sep 29, 2010 9:55 pm |
|
|
|
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
|
|