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
Go to Top of Page

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
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

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



--
Chandu
Go to Top of Page
   

- Advertisement -