I have 2 tables products
and catagories
connected by foreign key.
I need to update field products.new_cost
using field catagories.price_markup
as following:
UPDATE products p
INNER JOIN categories c ON p.category_id = c.id
SET p.new_cost = ROUND(p.pleer_cost * (1 + c.price_markup/100), -1)
WHERE p.update = 1
In SQL it's so easy, but how to do it using Django ORM?
My simplified try doesn't work Cannot resolve keyword 'category.price_markup' into field.
:
Product.actived.select_related('category').filter(update=1)).update(new_cost=F('pleer_cost') * F('category.price_markup'))
You cannot use F, but you can use Subquery and OuterRef:
from django.db.models import Subquery, OuterRef
cost = Category.objects.filter(
id=OuterRef('category_id')
).values_list(
'price_markup'
)[:1]
Product.objects.update(
new_cost=Subquery(cost)
)
OuterRef
which implements this feature. Check Andrey Berenda answer.According to the documentation, updates using join clauses are not supported, see:
However, unlike F() objects in filter and exclude clauses, you can’t introduce joins when you use F() objects in an update – you can only reference fields local to the model being updated. If you attempt to introduce a join with an F() object, a FieldError will be raised:
# THIS WILL RAISE A FieldError >>> Entry.objects.update(headline=F('blog__name'))
Also, according to this issue, this is by design and there is no plans to change it in the near future:
The actual issue here appears to be that joined F() clauses aren't permitted in update() statements. This is by design; support for joins in update() clauses was explicitly removed due to inherent complications in supporting them in the general case.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With