Update Rows with Data from another table

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update Rows with Data from another table

Author

Topic

jgallen23
Starting Member

17 Posts

Posted - 2006-11-15 : 17:48:42

I'm creating a stats table based on another table with:
select userid,count(*) as votes from votes group by userid

and I'd like to update the userstats table with that data. I'm not sure how to write the update command so that the votes update the corresponding user.

does this make sense?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-15 : 18:12:03

[code]update userstats
set votes = (select count(*)
from votes
where votes.userid = userstats.userid)[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-15 : 20:22:01

Why dont you use join?

Select u.userid, v.votes from userstats u inner join
(Select userid,count(*) as votes from votes group by votes) v
on u.userid=v.userid

Madhivanan

Failing to plan is Planning to fail

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-15 : 20:39:38

quote:
Why dont you use join?

It's an update, not a select.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-15 : 22:18:46

You can do an UPDATE with a join to a derived table in the FROM clause .


update a
set
	votes = b.votes
from
	userstats a
	join
	(
	select
		bb.userid,
		votes = count(*)
	from
		votes bb
	group by
		bb.userid
	) b
	on a.userid = b.userid

CODO ERGO SUM

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-15 : 22:53:03

Sure, but is that simpler than

update userstats
set votes = (select count(*)
from votes 
where votes.userid = userstats.userid)

I haven't analyzed it but I don't think the performance will be different, certainly worth testing though.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-15 : 22:57:39

Hard to say without seeing the query plan and testing the performance. They might even get the same query plan.

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-15 : 23:50:40

quote:
Originally posted by snSQL

quote:
Why dont you use join?

It's an update, not a select.

What I meant is, if it is for display there is no need of having seperate column and update it. It can be shown using slect query. Otherwise userstats table should be updated whenever new data are inserted to votes table

Madhivanan

Failing to plan is Planning to fail

cijuvarghese
Starting Member

12 Posts

Posted - 2006-11-16 : 03:27:34

Is it really works to update without join? because u r making a derived query here. wen i checked its not working.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-16 : 03:33:42

quote:
Originally posted by cijuvarghese

Is it really works to update without join? because u r making a derived query here. wen i checked its not working.

Can you post the query with the error you got?

Madhivanan

Failing to plan is Planning to fail

cijuvarghese
Starting Member

12 Posts

Posted - 2006-11-16 : 04:07:20

declare @temtable table(myid int,sumvalue numeric(18,2))
declare @temtable2 table(myid2 int,sumvalue2 numeric(18,2))
insert into @temtable(myid, sumvalue) values (1, 100)
insert into @temtable(myid, sumvalue) values (1, 100)
insert into @temtable(myid, sumvalue) values (2, 100)
insert into @temtable(myid, sumvalue) values (2, 100)
insert into @temtable(myid, sumvalue) values (2, 100)
insert into @temtable(myid, sumvalue) values (3, 100)

insert into @temtable2(myid2) values (1)
insert into @temtable2(myid2) values (2)
insert into @temtable2(myid2) values (3)

update @temtable2
set sumvalue2 = (select count(*)
from @temtable
where @temtable.myid = @temtable2.myid2)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-16 : 05:36:47

You cant directly use table variable as alias name. You need different alias name as given below

declare @temtable table(myid int,sumvalue numeric(18,2))
declare @temtable2 table(myid2 int,sumvalue2 numeric(18,2))
insert into @temtable(myid, sumvalue) values (1, 100)
insert into @temtable(myid, sumvalue) values (1, 100)
insert into @temtable(myid, sumvalue) values (2, 100)
insert into @temtable(myid, sumvalue) values (2, 100)
insert into @temtable(myid, sumvalue) values (2, 100)
insert into @temtable(myid, sumvalue) values (3, 100)

insert into @temtable2(myid2) values (1)
insert into @temtable2(myid2) values (2)
insert into @temtable2(myid2) values (3)

update @temtable2 
	set sumvalue2 = (select count(*)
from @temtable t1
	where t1.myid = t2.myid2)
from @temtable2  t2

select * from @temtable2

Madhivanan

Failing to plan is Planning to fail

cijuvarghese
Starting Member

12 Posts

Posted - 2006-11-16 : 05:41:42

oops!!!! very sorry.. 4getting the basics :-(

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-16 : 05:48:43

quote:
Originally posted by cijuvarghese

oops!!!! very sorry.. 4getting the basics :-(

You need to be careful when using table variables

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources