Jump to content

Sql price update


Recommended Posts



I recently CSV imported several thousand of products into prestashop (1.6). Several hundred of them got the price wrong. Now thats not a big problem cause I can just run SQL statement equivalent of find&replace on those.


The problem: in database I can only find pre-tax prices. I am not at all interested in pre-tax prices. I want to mass update FINAL RETAIL prices.


I could theoretically manually subtract 22% (my country's tax) from final retail price and enter those. But thats a lot of extra work and I am worried rounding would get messy. My client won't be happy to see his prices go from 9.90€, 19.90€ etc to some botched 9.87€ or whatever.


I did a search on the whole database and a part from the table for layered module there is no mention of the final prices at all.


Any help would be greatly appreciated.


TLDR; I need to mass change final retail prices, but i can only find pre-tax prices, which are pretty much useless.

Link to comment
Share on other sites

Create a spreadsheet and deduct tax. Then copy the whole D column with SQL queries:

    A          B                    C            D
1   tax incl.  tax excl.            id_product   SQL query
2   122.00     =ROUND(A2/1.22; 2)   777          ="UPDATE ps_...... SET price = "&B2&" WHERE id_product = "&C2&";"
Link to comment
Share on other sites

  • 2 months later...

@tuk66 I am having the same issue (migrated from 1.5.4 to 1.6.11)

All our prices without taxes are correct and even if the price with taxes will be displayed with "weird" cents, that wouldn't be a problem as some of our products actually have final prices such as 8.17€.


My issue is more or less the same that a lot of users are trying to fix: apply the tax rule and update the prices.


For the first part my solution would be 

update ps_product set id_tax_rules_group = '4';
update ps_product_shop set id_tax_rules_group = '4';

Now for the second part, updating the final prices by (price without tax) * 1.22 I am really not sure about what SQL I have to run.


Can anybody help?

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...