467,149 Members | 1,334 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,149 developers. It's quick & easy.

Subquery in UPDATE statement

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
  • viewed: 4390
Share:
2 Replies
code green
Expert 1GB
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
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.

Similar topics

22 posts views Thread by Kevin Murphy | last post: by
6 posts views Thread by phillip.s.powell@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.