How to update multiple rows in MySQL (CASE, WHEN, THEN) using python/Django

Question

I asked this question yesterday but It didn’t really help me. I am asking it again hoping my question is more clear this time. I am trying to update multiple rows in MySQL by using WHEN, THEN, ELSE case. I am trying to achieve this using python and Django in the backend. Here is my code:

UPDATE priority
SET priority_order =
    CASE priority_order
    WHEN 4 THEN 8
    WHEN 1 THEN 4
    WHEN 3 THEN 2

         ## More X amount of WHEN/THEN statements 

    ELSE priority_order
END

The problem is that I do not know how many WHEN and THEN lines I have.. How can I construct such a statement so that I can execute this using cursor.execute( )?

Yesterday, someone advised to build a list of expression and pass as an argument like:

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

But seems like it does not work on Mysql. I think this only works on SQLLite. The *when_list is not being decoded as my query execute. Please help!

Possible duplicate of How to programmatically execute WHEN and THEN on MySQL update using Python and Django? — iklinac
– iklinac, Commented Sep 22, 2017 at 18:34

score 0 · Accepted Answer · 2017-09-22 20:03:12Z

0

Can you try it:

when_data = [(2, 3), (3, 4), ] # Your method for generate the data
when_list = [When(priority_number=a, then=b) for a, b in when_data]
default = 90000 # Your value here

# Change YourModel
qs = YourModel.objects.update(priority_number=Case(
        *when_list,
         default=default
    ))
 # Now you can look on sql string
 from django.db import connection
 print (connection.queries[-1])

edited Sep 22, 2017 at 20:03

answered Sep 22, 2017 at 18:42

user8060120

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

7 Comments

Marvin Over a year ago

Hello, thank you for the comment. I get an error of ""You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*when_list,\n default=default\n"

Marvin Over a year ago

I am using mysql to execute the update. So instead of Models.objects.update() I am using cursor.execute(""" .... then code """) May I ask what is the correct syntax in mysql?

user8060120 Over a year ago

do you have a model, why are you use cursor?

Marvin Over a year ago

I have a model. But I am connected to mysql for my database.

user8060120 Over a year ago

updated the answer, you can look for syntax if need, but i don understand why you can not to use django methods.

|

Collectives™ on Stack Overflow

How to update multiple rows in MySQL (CASE, WHEN, THEN) using python/Django

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related