We are moving from a custom web store to a WooCommerce store and I need some help on the best way to update the orders status in the WooCommerce MySQL table. I have setup an ESS to the MySQL tables and have been able to create orders in our FMP current structure using the WooCommerce tables. However, I can't figure out how to update the status field for a specific order in the WooCommerce table. I have very little experience using SQL.
Should I use ESS or REST API?
I've been trying to use ESS but when I try updating the field directly, I get a validation error. I haven't been able to figure out the correct syntax for the Execute SQL UPDATE script step, specifically how to set the WHERE parameter to correct record. (Not even sure that's the correct solution.)
Would using the REST API be a better solution? We have hundreds of orders on a daily basis so performance is factor.