Create, update, delete, and query orders.

Table-Specific Information

Select

The adapter uses the Shopify API to process search criteria that refer to the Id, FinancialStatus, FulfillmentStatus, CreatedAt, ProcessedAt and UpdatedAt columns. The supported SQL operators are '=' and 'IN' for Id, '=' for FinancialStatus, and FulfillmentStatus, and '>' and '<' for the CreatedAt, ProcessedAt, and UpdatedAt columns. The adapter processes other filters client-side within the adapter.

For example, the following queries are processed server side:

SELECT * FROM Orders

SELECT * FROM Orders WHERE Id='123'

SELECT * FROM Orders WHERE FinancialStatus = 'pending'

SELECT * FROM Orders WHERE CreatedAt > '2017-10-25'

Insert

• Create a simple order with only a product variant Id using aggregates.
  

  INSERT INTO Orders(LineAggregate) VALUES('[{\"variant_id\":\"4236041945111\",\"quantity\":2}]')

• Create a simple order with only a product variant Id using temporary table. The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For instance:
  

  INSERT INTO OrdersItems#Temp (ItemVariantId,ItemQuantity) VALUES ('123',2)

  Once your temporary table is populated, it is now time to insert to the actual table in Shopify. You can do this by performing an INSERT to the actual table and setting as a value for LinesAggregate the name of the temporary table. For instance:
  

  INSERT INTO Orders (LineAggregate) VALUES ('OrdersItems#Temp')

• Create a simple order, sending the order receipt and fulfillment receipt to the customer using aggregates.
  

  INSERT INTO Orders(Email, FulfillmentStatus, SendReceipt, SendFulfillmentReceipt, LineAggregate) VALUES('foo@example.com', 'fulfilled', true, true, '[{\"variant_id\":\"4236041945111\",\"quantity\":2},{\"variant_id\":\"4236069011479\",\"quantity\":3}]')

• Create a simple order, sending the order receipt and fulfillment receipt to the customer using temporary table.
  

  INSERT INTO OrdersItems#Temp (ItemVariantId,ItemQuantity) VALUES ('4236041945111',2)
  INSERT INTO OrdersItems#Temp (ItemVariantId,ItemQuantity) VALUES ('4236069011479',3)
  INSERT INTO Orders (Email, FulfillmentStatus, SendReceipt, SendFulfillmentReceipt,LineAggregate) VALUES ('foo@example.com', 'fulfilled', true, true,'OrdersItems#Temp')

• Create a simple order, with taxes using aggregates.
  

  INSERT INTO Orders(LineAggregate, TaxAggregate) VALUES('[{\"variant_id\":\"4236041945111\",\"quantity\":2},{\"variant_id\":\"4236069011479\",\"quantity\":3}]', '[{\"price\":10.2,\"rate\":0.06,\"title\":\"State Tax\"}]')

• Create a simple order, with taxes using temporary table.
  

  INSERT INTO OrdersItems#Temp (ItemVariantId,ItemQuantity) VALUES ('4236041945111',2)
  INSERT INTO OrdersItems#Temp (ItemVariantId,ItemQuantity) VALUES ('4236069011479',3)
  INSERT INTO TaxItems#Temp (TaxItemPrice, TaxItemRate,TaxItemTitle) VALUES (10.2, 0.06, 'State Tax')
  INSERT INTO Orders (LineAggregate,TaxAggregate) VALUES ('OrdersItems#Temp','TaxItems#Temp')

• Create a simple order, with multiple taxes using aggregates.
  

  INSERT INTO Orders(LineAggregate, TaxAggregate) VALUES('[{\"variant_id\":\"4236041945111\",\"quantity\":2},{\"variant_id\":\"4236069011479\",\"quantity\":3}]', '[{\"price\":10.2,\"rate\":0.06,\"title\":\"State Tax\"},{\"price\":4.25,\"rate\":0.025,\"title\":\"County Tax\"}]')

• Create a simple order, with multiple taxes using temporary table.
  

  INSERT INTO OrdersItems#Temp (ItemVariantId,ItemQuantity) VALUES ('4236041945111',2)
  INSERT INTO OrdersItems#Temp (ItemVariantId,ItemQuantity) VALUES ('4236069011479',3)
  INSERT INTO TaxItems#Temp (TaxItemPrice, TaxItemRate,TaxItemTitle) VALUES (10.2, 0.06, 'State Tax')
  INSERT INTO TaxItems#Temp (TaxItemPrice, TaxItemRate,TaxItemTitle) VALUES (4.25, 0.025, 'County Tax')
  INSERT INTO Orders (LineAggregate,TaxAggregate) VALUES ('OrdersItems#Temp','TaxItems#Temp')

• Create a simple partially paid order containing a captured transaction using aggregates.
  

  INSERT INTO Orders(LineAggregate, FinancialStatus, TransactionAggregate) VALUES('[{\"title\":\"Big Brown Bear Boots\",\"price\":25.99,\"quantity\":2}]', 'partially_paid', '[{\"status\":\"success\",\"kind\":\"capture\",\"amount\":52.57,\"gateway\":\"bogus\"}]')

• Create a simple partially paid order containing a captured transaction using temporary table.
  

  INSERT INTO OrdersItems#Temp (ItemTitle,ItemQuantity,ItemPrice) VALUES ('Big Brown Bear Boots',2,25.99)
  INSERT INTO Transactions#Temp (TransactionItemAmount, TransactionItemKind, TransactionItemStatus, TransactionItemGateway) VALUES (52.57, 'capture', 'success', 'bogus')
  INSERT INTO Orders (LineAggregate,FinancialStatus,TransactionAggregate) VALUES ('OrdersItems#Temp','partially_paid','Transactions#Temp')

• Create a more comprehensive order using aggregates.
  

  INSERT INTO Orders(LineAggregate) VALUES([{\"title\":\"Big Brown Bear Boots\",\"price\":74.99,\"grams\":1300,\"quantity\":3}])

• Create a more comprehensive order using temporary table.
  

  INSERT INTO OrdersItems#Temp (ItemTitle,ItemPrice,ItemGrams,ItemQuantity) VALUES ('Big Brown Bear Boots', 74.99, 1300, 3)
  INSERT INTO Orders (LineAggregate) VALUES ('OrdersItems#Temp')

• Create a pending order with an existing customer using aggregates.
  

  INSERT INTO Orders(LineAggregate, CustomerId, FinancialStatus) VALUES('[{\"variant_id\":\"123\",\"quantity\":3}]', '456', 'pending')

• Create a pending order with an existing customer using temporary table.
  

  INSERT INTO OrdersItems#Temp (ItemVariantId, ItemQuantity) VALUES ('123', 3)
  INSERT INTO Orders (LineAggregate,CustomerId, FinancialStatus) VALUES ('OrdersItems#Temp','456', 'pending')

• Create a partially paid order with a new customer and addresses using aggregates.
  

  INSERT INTO Orders(LineAggregate, CustomerFirstName, CustomerLastName, CustomerEmail, FinancialStatus) VALUES('[{\"variant_id\":\"123\",\"quantity\":3}]', 'Paul', 'Norman', 'paul.norman@example.com', 'partially_paid')

• Create a partially paid order with a new customer and addresses using temporary.
  

  INSERT INTO OrdersItems#Temp (ItemVariantId, ItemQuantity) VALUES ('123', 3)
  INSERT INTO Orders(LineAggregate, CustomerFirstName, CustomerLastName, CustomerEmail, FinancialStatus) VALUES('OrdersItems#Temp', 'Paul', 'Norman', 'paul.norman@example.com', 'partially_paid')

• Close an order (you must specify the Id of the order).
  

    INSERT INTO Orders(Id, Operation) VALUES('123', 'Close')
    

• Reopen an order (you must specify the Id of the order).
  

    INSERT INTO Orders(Id, Operation) VALUES('123', 'Open')
    

• Cancel an order (you must specify the Id of the order).
  

    INSERT INTO Orders(Id, Operation) VALUES('123', 'Cancel')
    

Update

Only columns BuyerAcceptsMarketing, Email, Phone, Note, Tags, ShippingAddressFirstName, ShippingAddressLastName, ShippingAddressAddress1, ShippingAddressAddress2, ShippingAddressPhone, ShippingAddressCity, ShippingAddressCompany, ShippingAddressZip, ShippingAddressProvince, ShippingAddressCountry, ShippingAddressLatitude, ShippingAddressLongitude, ShippingAddressName, ShippingAddressCountryCode, ShippingAddressProvinceCode and ShippingAddressDefault can be updated.

• Update the shipping address of an existing order.
  

  UPDATE Orders SET ShippingAddressAddress1='123 Ship Street', ShippingAddressCity='Shipsville' WHERE Id='123'

• Update an order's tags.
  

  UPDATE Orders SET Tags='External, Inbound, Outbound' WHERE Id='123'

Delete

You must specify the Id of the order when deleting an item from this table.

DELETE FROM Orders WHERE Id='123'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.