September 24, 2007 at 7:16 am
I have a Reservation Table (R) which has two mutually exclusive columns GuestID and CompanyID which are Foreign Keys to Guest Table (G) and Company Table (C) respectively. Only one of the two columns will have a value greater than 0 in any row, the other one will always be 0.
The Reservation Table has a Foreign Key Res_No in InvoiceMaster Table (I), which in turn has a Foreign Key TaxInv_No in PaymentDetails Table (D), which has a Foreign Key PayNo in PaymentMaster Table (P).
I have now incorporated a Column P.CustID which I want to update with the higher value of R.GuestID and R.CompanyID with joins on D, I, and R.
Further, If R.GuestID > 0 Then P.CustID = R.GuestID * -1 ,
Else If R.CompanyID > 0 Then P.CustID = R.CompanyID.
Before I can implement the code changes in my application, I'll have to update the new column with a global update.
What is the shortest and best way to write the UPADTE Statement with all the joins and the Case logic?
Thanks.
September 24, 2007 at 11:35 pm
goodguy,
The following implementation assumes that the foreign keys have the same column names in the respective tables. If should find that the following example does not work, please double check and verify that the column names are correct.
Try this:
UPDATE
p
SET
CustID =
CASE
WHEN(r.GuestID>0)
THEN r.GuestID * -1
ELSE
r
.CompanyID
END
from
dbo.Reservation r
inner
join dbo.InvoiceMaster i
on
r.Res_No = i.Res_No
inner
join dbo.PaymentDetails d
on
i.TaxInv_no = d.TaxInv_no
inner
join dbo.PaymentMaster p
on
d.PayNo = p.PayNo
Regards,
Wameng Vang
MCTS
September 25, 2007 at 3:50 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply