CODEWITHSUNDEEP
pythonmysqldjango
Marvin
Marvin

Reputation: 243

How to programmatically execute WHEN and THEN on MySQL update using Python and Django?

I would like to update multiple rows in one single query by using UPDATE CASE scenario in mySQL. I am building my web app using python and Django. Here is my code:

UPDATE order
SET priority_number =
    CASE priority_number
    WHEN 2 THEN 3  
    WHEN 3 THEN 4
    WHEN 1 THEN 5
    WHEN 4 THEN 2
    WHEN 5 THEN 1
END

So this single query will update all field as I desire. My question is, how can I program this if I have an unknown number of rows to update? Lets say all these numbers comes from an array that I will pass into my views and I don’t know how many WHEN and THEN statement I need to write? thanks for your help!

Upvotes: 1

Views: 62

Answers (1)

NS0
NS0

Reputation: 6096

You can programmatically build up a query, using the conditionals Case and When.

So, for example:

from django.db.models import Case, When

# Make a list of When expressions
when_list = [When(priority_number=2, then=3),
             When(priority_number=3, then=4)]

# Use in a query of your choice, as an example:
Model.objects.update(
    priority_number=Case(
        *when_list))

Upvotes: 1

Related Questions