update question 1

[mjd3000]

I am trying to do an update of a field based where I am getting the data from a join between 2 other tables. Can somebody tell me what is wrong with my syntax below?

update ContactExtensionBase
set New_DateEnquiryTaken = (
SELECT l.createdon
FROM ContactBase c
inner join LeadBase l
on c.OriginatingLeadID = l.LeadID) as z
where ContactID = z.ContactID

 

[bborissov]

update ContactExtensionBase
set New_DateEnquiryTaken = Tb1l.createdon
FROM ContactExtensionBase
INNER JOIN (SELECT [c or l here].ContactID, l.createdon
                  FROM ContactBase c
                  inner join LeadBase l
           on c.OriginatingLeadID = l.LeadID) Tbl1
where ContactExtensionBase.ContactID = Tbl1.ContactID

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Emtucifor]

The parenthetical stuff is just clouding the issue. How about this way:

UPDATE E
SET New_DateEnquiryTaken = L.CreatedOn
FROM
   ContactExtensionBase E
   INNER JOIN ContactBase C ON E.ContactID = C.ContactID
   INNER JOIN LeadBase L ON C.OriginatingLeadID = L.LeadID

That seems a lot easier to understand, to me.