Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update Multiple Columns From Subquery Expand / Collapse
Author
Message
Posted Wednesday, August 19, 2009 1:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 21, 2010 12:28 PM
Points: 2, Visits: 6
Hi there,

I'm trying to use the following SQL (in SQL Server 2005) which works in Oracle:

UPDATE myschema.customers 
SET (OID, FCID) =
(SELECT X.OID_ACTUAL, X.FCID_ACTUAL FROM XREF_TABLE X
WHERE myschema.customers.OID = X.OID AND myschema.customers.FCID = X.FCID)
WHERE OID > 0 AND FCID > 0

I'm getting syntax errors at the 'SET' portion of the query. Am I missing something obvious?

Thank you for your help.
-sd
Post #773837
Posted Wednesday, August 19, 2009 1:47 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:19 PM
Points: 4,360, Visits: 9,543
Yeah - that doesn't work in 2005. It might work in 2008 - but I wouldn't count on it.

Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #773870
Posted Wednesday, August 19, 2009 1:56 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
You do that with Update...From in T-SQL.

Would look like this:
update cust -- use the alias for the target table
set OID = OID_ACTUAL, FCID = FCID_ACTUAL
from myschema.customers cust -- alias the target table
inner join xref_table xref
on cust.OID = xref.OID
and cust.FCID = xref.FCID
where cust.OID > 0
and cust.FCID > 0;



- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #773874
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse