CorrectTransDetailShippingAddressID

Explanation of SQL

Sets the ShippingAddressID in the TransDetail table to the correct ID using the ShippingAddressLinkID. This query is automatically ran in versions 04.50.1012.1701 and later during an update for the TransDetail table (Table version 44).

Risk of Data Corruption if Run Improperly

High. Data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.

SQL

UPDATE TransDetail
SET   ShippingAddressID = Al.AddressID
    , ModifiedDate = GETDATE()
    , ModifiedByUser = 'Correct ShippingAddressID'
FROM TransDetail TD WITH(NOLOCK)
  LEFT OUTER JOIN TransHeader TH ON TH.ID = TD.TransHeaderID
  LEFT OUTER JOIN AddressLink AL WITH(NOLOCK) ON Al.ID = TD.ShippingAddressLinkID
WHERE Al.AddressID <> TD.ShippingAddressID
  AND AL.ID IS NOT NULL
  AND TD.ShippingAddressID IS NOT NULL
  AND TH.TransactionType IN (1,6) AND TH.StatusID IN (1,2,3)

Version Information

  • Entered : 12/14/2010
  • Version : 04.50.1012.1701+

Related SQLs