Using MySQL to Update products_model
Hello,
I'm using v1.5.5e and have over 5000 products. I'd like to change the products_model using MySQL to standardize them. Can I run multiple update statements, for example:
UPDATE `products` SET `products_model` = 'BOR4000' WHERE `products`.`products_id` = 5205;
My concern is that there are fields/tables that relate to products_model that need updated as well. I understand the best way to accomplish this would be through the GUI however that is not an option for over 5000 products.
Installed plugins:
Sales Tax Summary
Easy Populate 4
Ceon URI Mapping (SEO)
Thanks!
Re: Using MySQL to Update products_model
Is the model always the same?
Re: Using MySQL to Update products_model
On v1.5.5e products_model only appears in 'products' and 'orders_products'. So I guess it depends on whether you have orders for the products you want to change the model of. You could do a test in the GUI to change the model number of a product you have an order for and see if the 'orders_products' is also changed - if it is then you could change that value using SQL statements as well.
Re: Using MySQL to Update products_model
Quote:
Originally Posted by
simon1066
On v1.5.5e products_model only appears in 'products' and 'orders_products'. So I guess it depends on whether you have orders for the products you want to change the model of. You could do a test in the GUI to change the model number of a product you have an order for and see if the 'orders_products' is also changed - if it is then you could change that value using SQL statements as well.
The 'orders_products' will not change. That would be wrong, and will destroy the order audit trail.
Re: Using MySQL to Update products_model
If you're using the latest version of EP4, then you could set the primary key to products_id, modify the products_model in your import and the product will take that products_model following the process of last entry for the products_id being the one that will be finally stored. No separate/tedious mysql related query needed.
Re: Using MySQL to Update products_model
Quote:
Originally Posted by
Design75
The 'orders_products' will not change. That would be wrong, and will destroy the order audit trail.
i'm a little confused by that.... if there are only 2 tables that make use of the products_model, how would changing orders_products destroy the audit trail? i would think just changing the products table w/o changing the orders_products would do more potential damage..... and i thought the two tables get joined via the products_id field?
just curious your thought process on that...
Re: Using MySQL to Update products_model
My model is always different. No two models are the same. It sounds like simply updating the `products_model` in the `products` table does not hurt anything and I won't need to change any other tables/columns. Is this correct?
In regards to EP4: I'm using Easy Populate 4.0.36.ZC - 07-05-2016. I'm assuming this is the latest?
You mention setting the primary key to products_id. I was unaware this setting existed. Thanks for the idea. I will try it although I receive a gateway timeout anytime I use EP. I'm assuming it is because I have over 20,000 products.
Re: Using MySQL to Update products_model
Quote:
Originally Posted by
cloud9
My model is always different. No two models are the same. It sounds like simply updating the `products_model` in the `products` table does not hurt anything and I won't need to change any other tables/columns. Is this correct?
Hurt "anything"? Well, that really depends on how/how else it is used on your site, but in a basic install of ZC, the products_model within the product's table could be modified without harm to the rest of ZC. By it being changed, there is a little bit of loss of correlation between ordered product and currently existing product. The need to maintain such a link also goes back to how all of the information is being used/kept/tracked.
Quote:
Originally Posted by
cloud9
In regards to EP4: I'm using Easy Populate 4.0.36.ZC - 07-05-2016. I'm assuming this is the latest?
You mention setting the primary key to products_id. I was unaware this setting existed. Thanks for the idea. I will try it although I receive a gateway timeout anytime I use EP. I'm assuming it is because I have over 20,000 products.
That is the latest published to ZC and yes it has that feature. May I suggest taking a look through the instructions, there is information there applicable to both issues. Yes, 20,000 items can cause some sort of issue, though there are some time settings that can be adjusted to make improvements. It is also possible to export in smaller chunks using the dropdown menu(s) along the top. But, it was not identified as to whether import or export is an issue. For import there is also the ability to split a long file into multiple parts so that each chunk can be processed individually rather than trying to import all at once and come across one of the timeouts...