CODEWITHSUNDEEP
pythondjangopostgresql
Micheal Griffin
Micheal Griffin

Reputation: 3

Is there a way to update a foreign key field using an SQL Update Query in Django

I have two classes, and I am attempting to move a field from one to the other as efficiently as possible. The database is large, so it's ideal if we can use an SQL Update query within Django or a direct SQL query. My database is PostgreSQL.

Class Order(models.Model):
    name = models.TextField(null=True, blank=True)
    address = models.ForeignKey(Address, on_delete=models.PROTECT)

Class Address(models.Model):
    name = models.TextField(null=True, blank=True)

I know I can do this manually, but I was hoping to be able to run an SQL Update query like so:

Order.objects.all().update(address__name=F('name'))

But I'm not able to update a foreign key field using the update command. Any suggestions for an alternative either in Django or directly in SQL - as opposed to just writing out the following - is appreciated.

for order in Order.objects.all():
    address = order.address
    address.name = order.name
    address.save()

Upvotes: 0

Views: 276

Answers (1)

Paweł Kordowski
Paweł Kordowski

Reputation: 2768

How about this:

orders = Order.objects.select_related('address')
addresses = []
for order in orders:
    address = order.address
    address.name = order.name
    addresses.append(address)
Address.objects.bulk_update(addresses, ['name'])

this should make only 2 SQL queries

but it is not well defined because one address can be attached to many orders

Upvotes: 1

Related Questions