Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2006-02-27 : 19:58:01
|
Argh. I have a feeling this one is obvious, but I can't seem to manage it.I'm looking to update a table variable using the results of a subquery, with part of the subquery's join criteria using the value of the row in the table variable.declare @t table(userId int primary key clustered,hsource int not null default(0),htarget int not null default(0))insert into @t(userId) select i_users from users_online WITH(NOLOCK)update @t set hsource= (select count(1) from users_online uo with (nolock) join user_rels ur with (nolock) on ur.i_users_source=userId and uo.i_users=ur.i_users_target and ur.rel_type=1 ) As you can see, the "on ur.i_users_source=userId" is trying to reference the userId from the row in @t that's being updated.No go ("invalid column name userId"). I've tried weird constructions like "update @t t" without luck either. What am I missing?Thanks-b |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-02-27 : 20:07:50
|
I think you are close...update @t set @t.hsource= count(1)from users_online uo with (nolock) join user_rels ur with (nolock) on ur.i_users_source=userId and uo.i_users=ur.i_users_target and ur.rel_type=1 Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda> |
 |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2006-02-27 : 20:27:13
|
| Argh. Thanks!-b |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2006-02-27 : 20:32:28
|
Oops. Spoke too soon -- that throwsAn aggregate may not appear in the set list of an UPDATE statement. -b |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-02-28 : 14:03:35
|
Hmm interesting....Try this update @t set @t.hsource= a.TheCount from (SELECT count(1) as TheCount FROM users_online uo with (nolock) join user_rels ur with (nolock) on ur.i_users_source=userId and uo.i_users=ur.i_users_target and ur.rel_type=1 ) a <Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda> |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2006-03-01 : 22:51:08
|
Thanks, Michael. That worked. I actually ended up doing update t set hsource= (select count(1) from users_online uo with (nolock) join user_rels ur with (nolock) on ur.i_users_source=t.userId and uo.i_users=ur.i_users_target and ur.rel_type=1 )from @t t , since the optimized liked that better. Why? I have no idea.-b |
|
|
|
|
|
|
Updating table variable using subquery?