By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Subquery in UPDATE statement

P: 2
I have a statement that would be valid in Oracle, but SQL Server doesn't like this form. I looked at the FROM clause and still can't figure out how to implement the statement... HELP!!

update STAGING_DSMGENERAL_REPORT D
set [Agency Name] =
(select substring([Agency Name],1,6) +' - ' + A.Name + ' - ' + C.FullName
from STAGING_DSMGENERAL_REPORT DSM,
Contact C,
Account A
where C.tsi_contactnumber = substring(DSM.[Agency Name],1,6)
and A.AccountId = C.AccountId
and DSM.[Proposal ID] = D.[Proposal ID])
where [Agency Name] like '%odified%'

Thanks for your help.
Pascal
Dec 8 '08 #1
Share this Question
Share on Google+
2 Replies


code green
Expert 100+
P: 1,726
I have a statement that would be valid in Oracle,

Are you asking us to port your Oracle queries to T-SQL for you?
If so why not ask that instead of the meaningless:-

but SQL Server doesn't like this form.

I think the problem is spaces in fieldnames
Square brackets [] valid in SQL 7.0
Dec 10 '08 #2

P: 2
Got my answer elsewhere.

For those interested....
UPDATE Table1 T1
SET FieldX = (SELECT T2.FieldY FROM Table2 T2 WHERE T2.KEY = T1.KEY)
WHERE ...

UPDATE Table1
SET FieldX = T2.FieldY
FROM Table1 T1
INNER JOIN Table2 T2 ON T2.KEY = T1.KEY
WHERE ....

Spaces are valid in brackets... in fact brackets are used to support spaces in field names and table names in SQL Server.
Dec 10 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.