May 8, 2005 at 8:02 pm
G'day lads and ladies,
I've decided to rewrite some old stored procs to make them more efficient
and decided to use table variables and everything is going well . .
except, I'm having some issues trying to use Update statements with the
table variable.
E.g. Update @TableVar Set Col1 = Stock.Description
From Stock
Where @TableVar.StockID = Stock.StockID
This kind of update statement is giving me grief. If I use the @TableVar
in the where clause it says that I need to define the variable?
I have been unable to use the Table variable in any where clause at all
but have solved this in other query types by using an alias in the From,
e.g. Select AliasX.StockID, AliasX.Description, Stock.SOH
FROM @TableVar AliasX
INNER JOIN Stock on AliasX.StockID = Stock.StockID
Where AliasX.ItemType = 500
Any help would be greatly appreciated
Regards
Adam
May 8, 2005 at 9:04 pm
If you use a from clause in an update statement, you should include the table you're updating. Try:
update @tablevar set col1 = stock.description
from @tablevar t join stock s on t.stockid = s.stockid
And of course, you can easily give it an alias in this situation.
Does this help?
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
May 8, 2005 at 10:41 pm
I disagree Rob,
Nothing personal, but I work with other database systems,
and if I specified the "table your're updating" in the from clause
(without aliasing it that is) it generally throws and error.
That's not to say it doesn't work in SQL Server, and in fact,
after posting up the question I went away and rewrote the queries
using your method.
It's just I don't like it, its conceptually wrong in my book.
May 8, 2005 at 11:11 pm
I agree that it seems wrong. And that other RDBMSs don't do it that way. Frequently I get frustrated at things in MS-SQL that seem wrong, given my Oracle background. But in MS-SQL, you should include the table you're updating in the from clause.
It's even more frustrating if you need to refer to the table you're updating twice!
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
May 8, 2005 at 11:22 pm
Thanks Rob.
There was a case where early on in my SQL Server life that I wrote a
query like
Update Bla Set Col1 = 'something' From Bla inner join yada on etc. With
out a where clause and I was absolutely shitting myself because I thought
it was updateing the entire table. Now I've learnt that for some reason SQL Server
doesn't do this.
So basically unlike other RDBMSs SQL Server assumes that in an update statement
if you reference the same table in the from clause without aliasing it is the
same instance/set?
-a-
May 8, 2005 at 11:34 pm
Yes. Although actually, what it actually does is calculate the from/where clause first, and then matches the table in your update clause with one of them.
With multiple references to the same table, you actually have to update an alias. Like this:
update o1 set orderdate = dateadd(year,-100,o1.orderdate)
from orders o1
join
orders o2
on o2.customerid = o1.customerid
and o1.employeeid = 1
and o2.employeeid <> 1
Which seems really wrong to me. But if you're coding in T-SQL, you just have to get used to it.
(Of course, I can't think why you'd want to shift the orderdate of employee #1's orders back 100 years... maybe as a practical joke?)
RobF
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply