SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Copy Sum Of A Column From Another Database		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

damdeok
Starting Member

39 Posts
Posted - 12/27/2012 :  03:09:15  Show Profile  Reply with Quote
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.
Edited by - damdeok on 12/27/2012 03:09:48

bandi
Flowing Fount of Yak Knowledge

India
1419 Posts
Posted - 12/27/2012 :  04:54:59  Show Profile  Reply with Quote 

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

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 - 12/27/2012 :  07:30:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1419 Posts
Posted - 12/27/2012 :  07:31:44  Show Profile  Reply with Quote
Welcome



--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000