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 2012 Forums
 Transact-SQL (2012)
 tsql help with updating multiple rows

Author  Topic 

parthhetal
Starting Member

2 Posts

Posted - 2013-02-13 : 18:42:37
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-13 : 18:53:56
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

52326 Posts

Posted - 2013-02-13 : 23:30:16
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
   

- Advertisement -