Subquery in UPDATE statement

[Zoopie911]

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

[code green]

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

[Zoopie911]

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.