MULTIPLE-JOINED TABLE UPDATE

• goodguy

  SSCertifiable

  Points: 5456

  More actions

  September 24, 2007 at 6:58 am

  #120503

  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
  

• mengus

  Ten Centuries

  Points: 1187

  More actions

  September 24, 2007 at 11:37 pm

  #735888

  Duplicate Posting......

  See http://www.sqlservercentral.com/Forums/Topic402003-8-1.aspx

  Regards,

  Wameng Vang

  MCTS