easiest way to batch update a mysql databse field with prices

G1rdy
G1rdy used Ask the Experts™
on
Hi Experts, I have an ecommerce store that is driven by a mysql database and I can access the database through PHPmyadmin.

In the database I can see the database table that has the prices of the products and I can download a full csv file of the items, all 1700 of them but I would like to change the prices by a fixed value on every line to meet recent changes in exchange rates. My fear is having never done anyting like this I just need a bit more confidence to make the change and hit the button and not destroy the thing!

So my question or questions are.

Should I download the full tables csv and use excel to update the prices by the required multipler of .9469 and then re-import the csv file into the database, if so how do I do this and what should I be aware of as the risks.

or

Can I run a command on the database through phpmyadmin just tchange the value field in that table by the required value of 0.9469?

Either way I need to be sure I do not destroy the database as it would take months to rebuild.

The fields in the database table "qkcv_catalog_product_entity_decimal" are:

value_id
entity_type_id
attribute_id
store_id
entitly_id
value

New to the raw database stuff so please be gentle with me.

Thanks in advance



Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Before running any mass update on the database, I'd recommend saving it to CSV so you can recover from a mistake.

To update your table, open the SQL tab on phpMyAdmin and enter:

update qkcv_catalog_product_entity_decimal set value = (value * .9469)

Author

Commented:
Thanks for the very rapid response, should I be aware of any settings when saving the CSV before running the update, being new to this I am just wanting to make sure I get it right.
Distinguished Expert 2017

Commented:
If this table only changes when you adjust prices, you can backup the table by using
mysqldump -u username -p'password' database_name table_name

If somehing goes wrong, you can restore the table contacts from this backup.
The CSV route you might be able to load it back in.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
arnold, really sorry for this question but can you give me a bit more detail of how and where I use this command, I really want to be safe...
Distinguished Expert 2017

Commented:
Oh, this a command line option.  I am not sure but think it might be possible to backup a table only within phpmyadmin.
http://fragments.turtlemeat.com/mysql-database-backup-restore-phpmyadmin.php

You might be able to achieve the same using the export feature and then use import if something goes wrong.

You can also perform a test by copying the current data from the table into a new 'temporary" table created in the same way.

i.e. get the detail on the way the current table is defined.
Then use the same script with the aleration of the table name to create the new temporary one.
then run
insert into new_temporary_table values (select * from current_price_table)

This way you have a table with the current data on which you can run the test mass update.

Then you can run a select that will display the relavent columns one next to another for comparison/visual inspection.
An easier way to backup your table is to use "select into" (you won't have to create the backup table ahead of time):

select * from source_table into backup_table

This creates a copy of "source_table" in "backup_table" - just know that if "backup_table" exists, any data it already contains will be blown away.
I think this tool will solve your problem - backup and update with GUI
http://www.phpmybackuppro.net/screenshots.php

Good luck!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial