title: How To Keep Records in Order from Derived Table
body: <p>I am trying to update a SQL table from a remote DB2 table. There may be multiple updates for the same record but I need the updates to happen in the order they are in in the DB2 table. You can not use Order By on a derived table. I have tried several different options to try to get this to work, but the updates still do not happen in order.</p>
  <p>For example:</p>
  <ul>
  <li>Change 1 - CUSTOMER NAME = ABCX</li>
  <li>Change 2 - CUSTOMER NAME = ABC</li>
  </ul>
  <p>After I run the query, the customer name is ABCX when it should be ABC.</p>
  <p>I truly do not know what else to try. I've tried temp tables (still derived table), creating a concatenated field with date and time fields, sub-select, row_number() over(order by date, time) and many other things. I'd like to keep it in order by the date and time fields in the remote table.</p>
  <p>Any insight would be appreciated.</p>
  <p>Thank you.</p>
  <p>Here is the basic code I have:</p>
  <pre><code>        SET
              A.CUSRID = B.RECORD_ID,
              A.CUSSTS = B.ACTIVE_CODE,
              A.CUSCOM = B.COMPANY_NUMBER,
              A.CUSMNM = B.CUSTOMER_NAME,
              A.CUSAD1 = B.CUSTOMER_ADDRESS_1,
              A.CUSAD2 = B.CUSTOMER_ADDRESS_2,
              A.CUSAD3 = B.CUSTOMER_ADDRESS_3,
              A.CUSZIP = B.CUSTOMER_ZIP_CODE,
              A.CUSZPE = B.CUSZPE_NOT_USED,
              A.CUSSTC = B.CUSTOMER_STATE,
              A.CUSARA = B.CUSTOMER_AREA_CODE,
              A.CUSPHN = B.CUSTOMER_PHONE,
              A.CUSB17 = B.CUSB17_NOT_USED,
              A.CUSTMT = B.STATEMENT_PRINT_CODE,
              A.CUSCRL = B.CREDIT_LIMIT,
              A.CUSCRC = B.CREDIT_CODE,
              A.CUSMCD = B.CUSMCD_NOT_USED,
              A.CUSTX1 = B.TAX_RATE_1,
              A.CUSTX2 = B.TAX_RATE_2,
              A.CUSTXC = B.TAX_RATE,
              A.CUSTXE = B.TAX_EXEMPT_ID,
              A.CUSB48 = B.CUSB48_NOT_USED,
              A.CUSMDT = B.MAINTENANCE_DATE,
              A.CUSB20 = B.CUSB20_NOT_USED,
              A.CSSRCH = B.SEARCH_FIELD,
              A.CUSBRN = B.BRANCH_ID,
              A.CUSDST = B.DISTRIBUTOR_NUMBER,
              A.CUSB28 = B.CUSB28_NOT_USED
      FROM 
          dbo.mcusmas A 
          INNER JOIN (
              SELECT
                  RECORD_ID,
                  ACTIVE_CODE,
                  COMPANY_NUMBER,
                  CUSTOMER_NUMBER,
                  CUSTOMER_NAME,
                  CUSTOMER_ADDRESS_1,
                  CUSTOMER_ADDRESS_2,
                  CUSTOMER_ADDRESS_3,
                  CUSTOMER_ZIP_CODE,
                  CUSZPE_NOT_USED,
                  CUSTOMER_STATE,
                  CUSTOMER_AREA_CODE,
                  CUSTOMER_PHONE,
                  CUSB17_NOT_USED,
                  STATEMENT_PRINT_CODE,
                  CREDIT_LIMIT,
                  CREDIT_CODE,
                  CUSMCD_NOT_USED,
                  TAX_RATE_1,
                  TAX_RATE_2,
                  TAX_RATE,
                  TAX_EXEMPT_ID,
                  CUSB48_NOT_USED,
                  MAINTENANCE_DATE,
                  CUSB20_NOT_USED,
                  SEARCH_FIELD,
                  BRANCH_ID,
                  DISTRIBUTOR_NUMBER,
                  CUSB28_NOT_USED,
              FROM remoteserver.MCUSMASPLG
              WHERE Event_State_ID = '*New' AND SENT_TO_DATA_WAREHOUSE = 'N'
          ) B
          ON A.CUSMNB = B.CUSTOMER_NUMBER
  </code></pre>