Update Muliple records from query 2

[JKDeveloper0718]

I have a query that has an ID and 3 fields as follows:

SELECT tblUnitApartment.HouseholdId, tblUnitApartment.UASecurityDeposit, tblUnitApartment.UASecurityDepositPet, tblUnitApartment.UASecurityDepositPetAd
FROM tblUnitApartment;

I have a table that has these same 3 fields and a HouseholdId called tblHoBackgroundAdmission. I just added these fields because I need to tranfer the data from one table to the other. What I need is to do an update on the tblHoBackgroundAdmission table where I set the 3 security deposit fields to the queries result based on the HouseholdId. In other words it will update each record in the tblHoBackgroundAdmission with the security deposit elements in the query above.

Suedo code:

update tblHoBackgroundAdmission
set tblUnitApartment.UASecurityDeposit, tblUnitApartment.UASecurityDepositPet, tblUnitApartment.UASecurityDepositPetAd
where SepQuery.HouseholId = tblHoBackgroundAdmission. HouseholdId
In (Select SepQuery.UASecurityDeposit, SepQuery.UASecurityDepositPet, SepQuery.UASecurityDepositPetAd
from SepQuery)

 

[PHV]

Something like this ?
UPDATE tblHoBackgroundAdmission AS H INNER JOIN tblUnitApartment AS U ON H.HouseholdId = U.HouseholdId
SET H.UASecurityDeposit = U.UASecurityDeposit, H.UASecurityDepositPet = U.UASecurityDepositPet, H.UASecurityDepositPetAd = U.UASecurityDepositPetAd

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[randy700]

How about...

[blue]UPDATE tblUnitApartment As A INNER JOIN
tblHoBackgroundAdmission As B ON 
A.HouseholdID = B.HouseholdID
SET A.UASecurityDeposit = B.UASecurityDeposit,
A.UASecurityDepositPet = B.UASecurityDepositPet,
A.UASecurityDepositPetAd = B.UASecurityDepositPedAd
[/blue]

Not Tested


Randy

 

[JKDeveloper0718]

Thanks guys they worked fine.

 

[Scooby62]

Hey Guys.

I have a similiar situation here but can't get it to work. Here's what I have so far:

Update EmpBen
Set EmpCov=(Case when Eml.AnnlFrzSal>=50000 then (35000+(Eml.AnnlFrzSal-50000)*.50) Else Eml.AnnlFrzSal*.70 End)
Where EmpBen.Ben='BasicLTD 07' AND EmpBen.Emp In(Select Eml.Emp,Eml.AnnlFrzSal,EmpBen.Ben From Emp
left join eml on (emp.emp = eml.emp) and emlefddt is null
left join empben on (empben.emp = eml.emp) and emlefddt is null)


Any ideas?

Thanks.

 

[randy700]

I think your problem may lie with the "Case" statement. I'd try changing it to an IIF.

[blue]IIF(Eml.AnnlFrzSal >= 50000, (35000 + (Eml.AnnFrzSal - 50000)) * .5, Eml.AnnlFrzSal * .7)[/blue]


Randy