Update Field in ONE Table given Values from Another Field

• eb24

  SSC Eights!

  Points: 823

  More actions

  April 14, 2004 at 1:55 pm

  #82656

  I have 2 tables named OutstandingInvoices and ClearedInvoices.  I have just set-up both tables and now I would like to link both tables somehow.  The OutstandingInvoices table has a field called Status with default set to 'Outstanding'.  The common field between the 2 tables is InvoiceNumber.

  I am trying to run some type of query statement where I would UPDATE the OutstandingInvoices table with Status = 'Cleared' if the pertinent Invoice Number is in the ClearedInvoices table.

  I am thinking somewhere down the lines of this (but still not confident):

  UPDATE OutstandingInvoices

  SET Status = 'Cleared'

  WHERE InvoiceNumber = (SELECT C.InvoiceNumber FROM ClearedInvoices C INNER JOIN OutstandingInvoices O ON C.InvoiceNumber = O.InvoiceNumber)

  Can someone please assist me on this initiative?  I will greatly appreciate any tips.

• bbychkov

  SSCrazy

  Points: 2206

  More actions

  April 14, 2004 at 4:22 pm

  #502829

  Try:

  UPDATE OutstandingInvoices

  SET Status = 'Cleared'

  FROM ClearedInvoices C INNER JOIN OutstandingInvoices O ON C.InvoiceNumber = O.InvoiceNumber