Django update table using data from another table

django django-orm django-database
Deadly picture Deadly · Sep 20, 2012 · Viewed 10.2k times · Source

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'))

Answer

Andrey Berenda picture Andrey Berenda · May 28, 2018

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)
)

Related questions

How to perform OR condition in django queryset?

I want to write a Django query equivalent to this SQL query: SELECT * from user where income >= 5000 or income is NULL. How to construct the Django queryset filter? User.objects.filter(income__gte=5000, income=0) This doesn't work, because it …

Read More
Django values_list vs values

In Django, what's the difference between the following two: Article.objects.values_list('comment_id', flat=True).distinct() vs Article.objects.values('comment_id').distinct() My goal is to get a list of unique comment ids under each Article. I've …

Read More
What's the difference between select_related and prefetch_related in Django ORM?

In Django doc, select_related() "follows" foreign-key relationships, selecting additional related-object data when it executes its query. prefetch_related() does a separate lookup for each relationship, and does the "joining" in Python. What does it mean by "doing the joining …

Read More