August 19, 2009 at 1:03 pm
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
August 19, 2009 at 1:47 pm
Yeah - that doesn't work in 2005. It might work in 2008 - but I wouldn't count on it.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 19, 2009 at 1:56 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply