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 2012 Forums
 Transact-SQL (2012)
 tsql help with updating multiple rows		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

parthhetal
Starting Member

Australia
2 Posts
Posted - 02/13/2013 :  18:42:37  Show Profile  Reply with Quote
i have a query as below.

1. i have a table in SAMINC database called ARCUS with column CODESLSP1
2. i need to update a table in CRM database called company with the value of CODESLSP1 in the column comp_sp
3. there are 1260 rows in table company

i tried a query as below but it fails as the sub query returns multiple rows
update company set comp_sp = (select CODESLSP1 from SAMINC.dbo.ARCUS INNER JOIN company ON SAMINC.dbo.ARCUS.IDCUST = comp_idcust COLLATE Latin1_General_BIN WHERE Comp_Deleted IS NULL)

please advise on how to best achieve and update multiple rows in company table from arcus table these both tables are in separate.
database

James K
Flowing Fount of Yak Knowledge

1744 Posts
Posted - 02/13/2013 :  18:53:56  Show Profile  Reply with Quote
Which table has the column Comp_Deleted? In the following, I am assuming it is in the company table. If not change it to a.Comp_Deleted.
UPDATE c SET 
	c.comp_sp = a.CODESLSP1
FROM
	company c
	INNER JOIN SAMINC.dbo.ARCUS a ON
		a.IDCUST = c.comp_idcust COLLATE Latin1_General_BIN
WHERE
	c.Comp_Deleted IS NULL;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48076 Posts
Posted - 02/13/2013 :  23:30:16  Show Profile  Reply with Quote
Are both these dbs in same server? if yes above will work

Otherwise you've to set up linked server before you do update like above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
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.05 seconds. Powered By: Snitz Forums 2000