Copy Sum Of A Column From Another Database

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 2005 Forums

Transact-SQL (2005)

Copy Sum Of A Column From Another Database

Author

Topic

damdeok
Starting Member

39 Posts

Posted - 2012-12-27 : 03:09:15

Friends, i have 2 dbs (db1 and db2). I need to copy the sum of points from player column to score with the following query:

update db1.dbo.player_info set score=sum(db2.dbo.player.points) where player_id=db2.dbo.player.member_id

I've tried this one:

update db1.dbo.player_info set score =(select sum(points)
FROM db2.dbo.player)
WHERE player_id = (select member_id
FROM db2.dbo.player)

but it gives me an error

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "SQL_Latin1_General_CP1_CS_AS" in the equal to operation.

I've also tried this two:

update db1.dbo.player_info set score =(select sum(points) collate Chinese_PRC_CI_AS
FROM db2.dbo.player)
WHERE player_id = (select member_id
FROM db2.dbo.player)

...

update db1.dbo.player_info set score =(select sum(points) collate SQL_Latin1_General_CP1_CS_AS
FROM db2.dbo.player)
WHERE player_id = (select member_id
FROM db2.dbo.player)

but it gives me the same error.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-27 : 04:54:59

[code]
UPDATE pi
SET score = SumOfPoints
FROM db1.dbo.player_info pi
JOIN (SELECT member_id, sum(points) SumOfPoints
FROM db2.dbo.player
GROUP BY member_id
) p
ON pi.player_id = p.member_id collate SQL_Latin1_General_CP1_CS_AS
[/code]
If you get error again, put collate SQL_Latin1_General_CP1_CS_AS option next to player_id in the condition

--
Chandu

damdeok
Starting Member

39 Posts

Posted - 2012-12-27 : 07:30:31

this one works great. thanks man.

UPDATE pi
SET score = SumOfPoints
FROM db1.dbo.player_info pi
JOIN (SELECT member_id, sum(points) SumOfPoints
	FROM db2.dbo.player
	GROUP BY member_id
     ) p
ON pi.player_id = p.member_id collate SQL_Latin1_General_CP1_CS_AS

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-27 : 07:31:44

Welcome

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources