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

Andrei Berenda · Accepted Answer · 2019-07-23 04:17:58Z

39

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

edited Jul 23, 2019 at 4:17

answered May 28, 2018 at 8:06

Andrei Berenda

1,9862 gold badges15 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Aamish Baloch Over a year ago

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

nnov Over a year ago

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

jbodily Over a year ago

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

Andrei Berenda Over a year ago

@jbodily 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

Cesar Canassa · Accepted Answer · 2019-08-31 08:59:42Z

19

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.

edited Aug 31, 2019 at 8:59

answered Apr 21, 2016 at 9:46

Cesar Canassa

20.5k13 gold badges68 silver badges70 bronze badges

3 Comments

Andrei Berenda Over a year ago

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

Cesar Canassa Over a year ago

@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.

Matthew Schinckel Over a year ago

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

Anatoly E · Accepted Answer · 2020-06-19 21:27:14Z

0

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

answered Jun 19, 2020 at 21:27

Anatoly E

1,14111 silver badges9 bronze badges

Collectives™ on Stack Overflow

Django update table using data from another table

3 Answers 3

4 Comments

Note: My answer is outdated now, Django 1.11 introduced `OuterRef` which implements this feature. Check Andrey Berenda answer.

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Note: My answer is outdated now, Django 1.11 introduced OuterRef which implements this feature. Check Andrey Berenda answer.

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Note: My answer is outdated now, Django 1.11 introduced `OuterRef` which implements this feature. Check Andrey Berenda answer.