Bulk update of a model field based on a condition of the other model fields-CodePudding

I have a 5 column model with around 10,000 lines of data. Now I want to update one column of all the lines based on other column values.

CodePudding user response：

In my opinion, for your scenario "I want to update one column of all the lines based on other column values.", the best option is to use Conditional update

Let's suppose this model:

class Person(models.Model):
    f1 = models.IntegerField()
    f2 = models.IntegerField()
    f3 = models.IntegerField()
    f4 = models.IntegerField()

You can update it in this way:

rom django.db.models import F, When, Case

new_value = Case(
    When(f1__gte=F('f2'), then=F('f1')),
    default=F('f2') 
)

Person.objects.update(f3=new_value)

This raise a simple update sql statement:

>>> import logging
>>> l = logging.getLogger('django.db.backends')
>>> l.setLevel(logging.DEBUG)
>>> l.addHandler(logging.StreamHandler())
>>> Person.objects.update(f3=c)
(0.000) UPDATE "tt_person" SET "f3" = CASE WHEN ("tt_person"."f1" >= "tt_person"."f2") THEN "tt_person"."f1" ELSE "tt_person"."f2" END; args=()
0

CodePudding user response：

The out-of-the-box django feature to do bulk update is bulk_update(). Here an example:

myModel = MyModel.objects.all()

for l in myModel:
    if c1=='ChkVal1' and c2 < c3:    
        c4='Value 1'
    elif c1=='ChkVal2' and c2 b> c3:
        c4='Value 2'
    else
        c4='Value 3'

MyModel.objects.bulk_update(myModel, update_fields = ['c4'])

Remember, quoting docs:

You cannot update the model’s primary key.

Each model’s save() method isn’t called, and the pre_save and post_save signals aren’t sent.

If updating a large number of columns in a large number of rows, the SQL generated can be very large. Avoid this by specifying a suitable batch_size.

Updating fields defined on multi-table inheritance ancestors will incur an extra query per ancestor.

When an individual batch contains duplicates, only the first instance in that batch will result in an update.