Change Product Model Numbers en masse?

What is the best way to mass replace existing products_models without changing products_ids; for a given manufctuer?

By mistake products_models are entered as numeric. Leaving out the manufacturers abbreviation, in-front of the model number.

Need to change from 123456 to GHD-123456.

What is the best way to do this, other than one by one through PHPMyAdmin?

Back up your database first because bad things happen accidentally.

UPDATE products SET products_model = CONCAT("GHD-",products_model);

This will update every product to have its model number prepended with "GHD-".

Thank you for the reply.

How would you limit this concatenation to a specific manufacturers_id from zen_manufacturers table?

Let's say the manufacturer_id you wanted to work with was 1. Here's what the query would look like:

UPDATE products SET products_model = CONCAT("GHD-",products_model) WHERE manufacturers_id = 1;

If you wanted to do this by name, you could, but it would require some joins.

allthingsidLeroy:

Let's say the manufacturer_id you wanted to work with was 1. Here's what the query would look like:

UPDATE products SET products_model = CONCAT("GHD-",products_model) WHERE manufacturers_id = 1;

> 
> If you wanted to do this by name, you could, but it would require some joins.

If this is for a small number of manufacturers I would use the above as its simple and clean.  However, if you are not familiar with SQL statements for modifying the database...be very careful...in fact..if that is the case, post what you want to change and let one of the experts give you the SQL statement to run as a mistake could be costly...BACKUP....BACKUP....BACKUP lol.

g2ktcf:

If this is for a small number of manufacturers I would use the above as its simple and clean. However, if you are not familiar with SQL statements for modifying the database...be very careful...in fact..if that is the case, post what you want to change and let one of the experts give you the SQL statement to run as a mistake could be costly...BACKUP....BACKUP....BACKUP lol.

Thank you for the advice. Don't know much about SQL statements.

My case is exactly as I stated.

I need to change / revise existing products_models without changing products_ids; for a defined manufacturer in the cart.

ie. Need to change products_models from 123456 to GHD-123456, for XYZ manufacturer (XYZ manufacturers_id is 5)

What is the SQL statement for the above?
Is it not the following:

UPDATE products SET products_model = CONCAT("GHD-",products_model) WHERE manufacturers_id = 5;

Kevin205:

Thank you for the advice. Don't know much about SQL statements.

My case is exactly as I stated.

I need to change / revise existing products_models without changing products_ids; for a defined manufacturer in the cart.

ie. Need to change products_models from 123456 to GHD-123456, for XYZ manufacturer (XYZ manufacturers_id is 5)

What is the SQL statement for the above?
Is it not the following:

UPDATE products SET products_model = CONCAT("GHD-",products_model) WHERE manufacturers_id = 5;

do you have a DB_PREFIX?  All of my tables have zen_ in front of them.....

if not, then the code above appears correct...BUT I AM NOT AN EXPERT BY ANY MEANS

Kevin205:

Is it not the following:

UPDATE products SET products_model = CONCAT("GHD-",products_model) WHERE manufacturers_id = 5;

That looks right to me.

> **g2ktcf:**
>
> do you have a DB_PREFIX?  All of my tables have zen_ in front of them.....
> 
> if not, then the code above appears correct...BUT I AM NOT AN EXPERT BY ANY MEANS

I forgot about this. But in PHPmyAdmin usually there's a place where you can 'use' a database. In any case, if you do have a prefix, you can just use that. This query isn't exactly all that complex.

allthingsidLeroy:

That looks right to me.

I forgot about this. But in PHPmyAdmin usually there's a place where you can 'use' a database. In any case, if you do have a prefix, you can just use that. This query isn't exactly all that complex.

All of my cart tables have zen_ prefix in SQL. So it would be:

UPDATE zen_products SET products_model = CONCAT("GHD-",products_model) WHERE manufacturers_id = 5;

This should be executed from PHPMyAdmin. Correct?

Kevin205:

All of my cart's tables have zen_ prefix in SQL. But in the following statement there is no mention of any table(s)!

UPDATE products SET products_model = CONCAT("GHD-",products_model) WHERE manufacturers_id = 5;

> 
> Why should the zen_ prefix matter?
> 
> 
> Should this be executed from cart's admin or form PHPMyAdmin?

if you run this from phpMyAdmin...and you have a prefix...then the code will error out.  I am not sure about running it from the cart's admin as I do not use that very frequently.

**ALSO...if you do this...put your store in maintenance mode just in case....oh I did say backup your database right?**  

If your database is backed up...you can simply restore it if the statement does something unexpected.

Got it and Thank you.

Kevin205:

UPDATE zen_products SET products_model = CONCAT("GHD-",products_model) WHERE manufacturers_id = 5;

As long as those column names match up to your, this seems correct.

Yes, you can execute this from PHPMyAdmin. There should be a place to execute custom queries.

Like I said, though, you should make a backup first.

Backup...

All the time.

Kevin205:

All of my cart tables have zen_ prefix in SQL. So it would be:

UPDATE zen_products SET products_model = CONCAT("GHD-",products_model) WHERE manufacturers_id = 5;

> 
> 
> This should be executed from PHPMyAdmin. Correct?

if "zen_" is your db prefix, then this is the correct code to run from phpMyAdmin

Understand that anything more complex than this simple task would not be done this way.

If I had to do anything more complex than this.... I would make a copy of my database and TEST the sql statement on that new copy. Then you evaluate the changes done by the SQL statement. If it works as expected, then I would do it on the real database.