Changing prices using SQL?

8

Hi Brian!
same issue one year on, have to keep increasing those prices during inflation (are you in the UK even?). Anyhow, as I have been finding my own answers to my own questions on stack overflow for decades, here’s another greeting from the past;)

Bulk-increase all prices by 6.8% on Jan 1st:

at 00:01 1.1.2024
at>mysql -u root -p[mysql password] -e 'USE [db name]; UPDATE `tabItem Price` SET price_list_rate = price_list_rate * 1.068 WHERE price_list = "Standard Selling";' 
at>
CONTROL-D

(adjust factor and constraints until tender)

Being an sql person, I have used the above approach a couple of times without problems now. Yet there might be repercussions in data consistency as tmatteson pointed out in 105729, so here’s his approach :

items = frappe.get_all('Item', {'item_group': ['in', ['item group', 'other item group']]}, 'name', pluck="name')

for item in frappe.get_all('Item Price', {'item_code': ['in', items]}):
           item_price = frappe.get_doc('Item Price', item)
           item_price.price_list_rate = item_price.price_list_rate * 1.068
           item_price.save()

frappe.db.commit()

Alas, with peterg’s explanation of the ORM (again in 105729) I see no harmful consequences with the vanilla sql method. But please correct me if I’m wrong.

Merry Christmas everyone!

1 Like