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.
Regards,
goodguy
Experience is a bad teacher whose exams precede its lessons