Using the Results of a query/view to Update a Table 2

[RedSparks]

I want to update the table FD__Agency_Admission with the results from a sql select query saved it as a View called TLV__ES_AgencyDischargeDates.

I want to do something like this:

Update FD__Agency_Admission
Set Discharge_Date = '12/31/2004'
Where Discharge_Date is null

How do I use the FamilyKey results in the view to UPDate the FD__Agency_Admission Table where the FamilyKey from the matches the FamilyKey in TLV__ES_AgencyDischargeDates?

Here's the original sql query:

SELECT t.FamilyKey, t.ProgramCode
FROM dbo.TLV_NullDischargeDate t INNER JOIN
(SELECT FamilyKey
FROM TLV_NullDischargeDate
GROUP BY FamilyKey
HAVING COUNT(FamilyKey) = 1) a
ON t.FamilyKey = a.FamilyKey
WHERE (t.ProgramCode = 6)

Results:

FamilyKey ProgramCode
84684 6
87510 6
88721 6
84362 6

 

[SQLSister]

Use a derived table in the update statment and join on the key field(s). The general form of an update doing this is below:

UPDATE table1
SET field1 = h.field1,
field2 = h.field2
FROM table1 JOIN (select idfield, Field1 from Holdingtable where field1 = 200) h
ON table1.idfield = h.idfield

Questions about posting. See faq183-874

 

[RedSparks]

This is what I did.


Update FD__Agency_Admission
Set FD__Agency_Admission.Discharge_Date = '12/31/2004'

FROM FD__AGENCY_ADMISSION RIGHT OUTER JOIN
(Select FamilyKey From TLV_ES_AgencyDischargeDates)h
on FD__Agency_Admission.FamilyKey = h.FamilyKey


The FamilyKeys listed in the TLV_ES_AgencyDischargeDates Updated correctly!

Thank You SQLSister, so much for your assistance with this and my previous question!