Django update table using data from another table

Question

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

Cestus

Answer 1

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

Denominate

As far as I got the idea of the relations between the two models, I would suggest the cost query filter to be like id=OuterRef('category_id') – Tuberculous

just for the record, this is not possible in Django 1.7 – Rowdyism

What if the primary key is on the referenced table instead of the target? – Relly

@Relly You can use like this Product.objects.update(new_cost=Subquery(Category.objects.values_list('price_marup')[:1])) But then all products will have the same new_cost – Denominate

Answer 2

Note: My answer is outdated now, Django 1.11 introduced 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.

Immobilize

Yes, you are right, but the solution exists, see my answer below – Denominate

@AndreyBerenda My answer predates Django 1.11, but I think it's still valid for people stuck with Django 1.11, I will edit the answer to point it to ours, thanks. – Immobilize

If you are on an older Django, then (a) you should upgrade, but (b) you can use the backport django-subquery: pypi.org/project/django-subquery – Sills

Answer 3

rows = Product.objects.filter(old_field__isnull=False)
for row in rows:
     row.new_field = row.old_field.subfield
Product.objects.bulk_update(rows, ['new_field'])

Saari