SQL Query: Update SubscriberID in Order Detail

The following steps outline the solution to updating Recipient field (Subscriber ID) for order lines when order cancellations error out. 

The screenshot below is an example of the error message received during cancellation that indicates an order line within the original order is missing recipient (or Subscriber ID).    

In Microsoft SQL (test: ahimasql-test or prod: ahimasql-01a), click to create new query and paste the following to search for order missing recipient (subscriber ID):  

-----------------------------------
--SEARCH FOR ORDER AND REVIEW MISSING INFO
-----------------------------------
select od.SubscriberID, om.ShipToID, od.*
from aptify.dbo.OrderDetail od
inner join aptify.dbo.OrderMaster om on od.OrderID = om.ID
where OrderID = 27872987 --this is the order ID

In the same query (a few rows below) paste the following to update the order line recipient (subscriber ID):

-----------------------------------
--UPDATE STATEMENT - RUN CAREFULLY
-----------------------------------
--UPDATE aptify.dbo.OrderDetail
--SET SubscriberID = 3498889 --this is the ShipToID
--WHERE OrderID = 27872987 --this is the OrderID
----AND ID = 24590974 --this is the row of the OrderDetailID, it's overkill but I like having a second field to check against

NOTE: Query line that start with "--" are in commenting mode. To uncomment, delete the -- or highlight rows to uncomment the selected lines (Ctrl+K,Ctrl+U).

Uncommenting lines in query

Attachments (2)

68e76e48dc0a....png
162 KB

68e76e48dc0a....png
270 KB