Post by JayFinlay » Sun Jan 29, 2023 7:04 pm

I just imported over 3000+ products from my wholesaler and a lot of the prices end in odd numbers, for example $68.13 or $42.39.
How do I update all products easily to all end with .95. For the above they would become $68.95 and $42.95.

I would appreciate this so much if someone could help me out.

Newbie

Posts

Joined
Sun Jan 29, 2023 7:00 pm

Post by paulfeakins » Mon Jan 30, 2023 8:00 pm

JayFinlay wrote:
Sun Jan 29, 2023 7:04 pm
I would appreciate this so much if someone could help me out.
First work out what you want the final price to be, perhaps by replacing the part after the decimal with .95, then divide by your tax rate to set your product price.

UK OpenCart Hosting | OpenCart Audits | OpenCart Support - please email info@antropy.co.uk


User avatar
Guru Member
Online

Posts

Joined
Mon Aug 22, 2011 11:01 pm
Location - London Gatwick, United Kingdom

Post by by mona » Mon Jan 30, 2023 8:30 pm

no matter what you do I suggest you backup the database &/or that table first

DISCLAIMER:
You should not modify core files .. if you would like to donate a cup of coffee I will write it in a modification for you.


https://www.youtube.com/watch?v=zXIxDoCRc84


User avatar
Expert Member

Posts

Joined
Mon Jun 10, 2019 9:31 am

Post by OpenCode3.0 » Tue Jan 31, 2023 1:45 am

JayFinlay wrote:
Sun Jan 29, 2023 7:04 pm
I just imported over 3000+ products from my wholesaler and a lot of the prices end in odd numbers, for example $68.13 or $42.39.
How do I update all products easily to all end with .95. For the above they would become $68.95 and $42.95.

I would appreciate this so much if someone could help me out.
@JayFinlay

Well if all you need is to update all products that have the same price you can do the following run this code in your phpmyadmin SQL window run this to find how many products

Code: Select all

 SELECT * FROM `product` WHERE `price` = '68.13';
then run the following in the same SQL window (clear the previous query)

Code: Select all

UPDATE `product` SET `price` = '68.95' WHERE `price` = '68.13';
then just do the same for the other price and make sure you do a backup for your products...
no joke!

OpenCode

Newbie

Posts

Joined
Sun Apr 17, 2022 1:51 am

Post by agatha65 » Tue Jan 31, 2023 2:26 am

@JayFinlay,
This module can import products with rounded prices.
Also has a tool to round the existing product prices.
https://www.opencart.com/index.php?rout ... n_id=44615

Suppliers Module - XML, CSV, XLS Product Feed Import and Update
Rich Snippets | Facebook Open Graph Meta Tags | WebP Images
Image


User avatar
Active Member

Posts

Joined
Fri Mar 16, 2012 10:18 am
Location - Canada, QC

Post by SohBH » Tue Jan 31, 2023 8:24 pm

Code: Select all

UPDATE `product` SET `price` = ROUND(`price`+0.05)-0.05 WHERE `price` > 0.5

Business Web Development | Content Creation | Analytics and Reporting | SEO


User avatar
Active Member
Online

Posts

Joined
Mon Nov 02, 2020 12:01 am
Location - Malaysia

Post by OpenCode3.0 » Tue Jan 31, 2023 11:45 pm

OpenCode3.0 wrote:
Tue Jan 31, 2023 1:45 am
JayFinlay wrote:
Sun Jan 29, 2023 7:04 pm
I just imported over 3000+ products from my wholesaler and a lot of the prices end in odd numbers, for example $68.13 or $42.39.
How do I update all products easily to all end with .95. For the above they would become $68.95 and $42.95.

I would appreciate this so much if someone could help me out.
@JayFinlay

Well if all you need is to update all products that have the same price you can do the following run this code in your phpmyadmin SQL window run this to find how many products

Code: Select all

 SELECT * FROM `product` WHERE `price` = '68.13';
then run the following in the same SQL window (clear the previous query)

Code: Select all

UPDATE `product` SET `price` = '68.95' WHERE `price` = '68.13';
then just do the same for the other price and make sure you do a backup for your products...
no joke!

OpenCode
@JayFinlay

you can use this query to update all prices to end in .95 basically what this query does is update anything after the (.) dot to .95, you can change the .95 to wherever number your need.

Code: Select all

UPDATE `product` SET `price` = REPLACE(`price`,'.','.95');
@SohBH
Thank you for your query SohBH I try it, and it rounds up to a full number and then deducted 5 so the price goes to 41.95 instead of 42.95.

@JayFinlay
if any of the solutions posted here help you to solve your problem don't forget to help those that help you, so free help and support can be offered from time to time.

I just got a buy me a coffee page.
is not obligatory but is appreciated if you can buy us a coffee.
https://www.buymeacoffee.com/opencode.donate

Newbie

Posts

Joined
Sun Apr 17, 2022 1:51 am

Post by paulfeakins » Thu Feb 02, 2023 10:53 pm

SohBH wrote:
Tue Jan 31, 2023 8:24 pm

Code: Select all

UPDATE `product` SET `price` = ROUND(`price`+0.05)-0.05 WHERE `price` > 0.5
Not bad but this won't account for tax.

UK OpenCart Hosting | OpenCart Audits | OpenCart Support - please email info@antropy.co.uk


User avatar
Guru Member
Online

Posts

Joined
Mon Aug 22, 2011 11:01 pm
Location - London Gatwick, United Kingdom
Who is online

Users browsing this forum: DesignCart and 100 guests