String concatenation by using the value of the model field while updating a queryset

Question

I want to update the email id of some users to: "prefix" + "value of the user email"

Now I can do this for one user as follows:

User.objects.filter(pk=<id>).update(email=Concat(Value("prefix"), 'email'))

However, as soon as I filter on a pk list, I get a nasty error. The query is:

User.objects.filter(pk__in=<list_id>).update(email=Concat(Value("prefix"), 'email'))

The error is:

/Users/zishanahmad/Devel/Env/venv_sliderule/lib/python2.7/site-packages/django/db/models/query.pyc in update(self, **kwargs)
    561         query.add_update_values(kwargs)
    562         with transaction.atomic(using=self.db, savepoint=False):
--> 563             rows = query.get_compiler(self.db).execute_sql(CURSOR)
    564         self._result_cache = None
    565         return rows

/Users/zishanahmad/Devel/Env/venv_sliderule/lib/python2.7/site-packages/django/db/models/sql/compiler.pyc in execute_sql(self, result_type)
   1060         related queries are not available.
   1061         """
-> 1062         cursor = super(SQLUpdateCompiler, self).execute_sql(result_type)
   1063         try:
   1064             rows = cursor.rowcount if cursor else 0

/Users/zishanahmad/Devel/Env/venv_sliderule/lib/python2.7/site-packages/django/db/models/sql/compiler.pyc in execute_sql(self, result_type)
    838         cursor = self.connection.cursor()
    839         try:
--> 840             cursor.execute(sql, params)
    841         except Exception:
    842             cursor.close()

/Users/zishanahmad/Devel/Env/venv_sliderule/lib/python2.7/site-packages/django/db/backends/utils.pyc in execute(self, sql, params)
     77         start = time()
     78         try:
---> 79             return super(CursorDebugWrapper, self).execute(sql, params)
     80         finally:
     81             stop = time()

/Users/zishanahmad/Devel/Env/venv_sliderule/lib/python2.7/site-packages/django/db/backends/utils.pyc in execute(self, sql, params)
     62                 return self.cursor.execute(sql)
     63             else:
---> 64                 return self.cursor.execute(sql, params)
     65 
     66     def executemany(self, sql, param_list):

/Users/zishanahmad/Devel/Env/venv_sliderule/lib/python2.7/site-packages/django/db/utils.pyc in __exit__(self, exc_type, exc_value, traceback)
     96                 if dj_exc_type not in (DataError, IntegrityError):
     97                     self.wrapper.errors_occurred = True
---> 98                 six.reraise(dj_exc_type, dj_exc_value, traceback)
     99 
    100     def __call__(self, func):

/Users/zishanahmad/Devel/Env/venv_sliderule/lib/python2.7/site-packages/django/db/backends/utils.pyc in execute(self, sql, params)
     62                 return self.cursor.execute(sql)
     63             else:
---> 64                 return self.cursor.execute(sql, params)
     65 
     66     def executemany(self, sql, param_list):

/Users/zishanahmad/Devel/Env/venv_sliderule/lib/python2.7/site-packages/django/db/backends/mysql/base.pyc in execute(self, query, args)
    122         try:
    123             # args is None means no string interpolation
--> 124             return self.cursor.execute(query, args)
    125         except Database.OperationalError as e:
    126             # Map some error codes to IntegrityError, since they seem to be

/Users/zishanahmad/Devel/Env/venv_sliderule/lib/python2.7/site-packages/MySQLdb/cursors.pyc in execute(self, query, args)
    224         except Exception:
    225             exc, value = sys.exc_info()[:2]
--> 226             self.errorhandler(self, exc, value)
    227         self._executed = query
    228         if not self._defer_warnings: self._warning_check()

/Users/zishanahmad/Devel/Env/venv_sliderule/lib/python2.7/site-packages/MySQLdb/connections.pyc in defaulterrorhandler(***failed resolving arguments***)
     34     del connection
     35     if isinstance(errorvalue, BaseException):
---> 36         raise errorvalue
     37     if errorclass is not None:
     38         raise errorclass(errorvalue)

OperationalError: (1093, "You can't specify target table 'auth_user' for update in FROM clause")

I don't really understand what the error message is trying to say. Any help would be appreciated.

EDIT: MySQL version: 5.7.12 Django: 1.8

That's because of `MySQL` and not Django. More [here](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) — nik_m, Mar 21 '17 at 17:44
@nik_m Any workarounds to get this working? I don't understand the reason why it doesn't work for multiple users and works for a single user. — Zishan Ahmad, Mar 21 '17 at 17:51

score 0 · Answer 1 · answered Mar 21 '17 at 18:04

0

What does your <list_id> look like? What's the result of User.objects.filter(pk__in=<list_id>)?

In Django 1.10, I have no problem running:

>>> User.objects.filter(pk=1).update(first_name='Test')
1L
>>> User.objects.filter(pk__in=[1, 2]).update(first_name='Test')
2L

As for workarounds, obviously it isn't as efficient, but why not just cache the QuerySet and then iterate over it to update each object? This is (hopefully) a one-time query, so I'm not sure how much of a concern SQL efficiency is.

answered Mar 21 '17 at 18:04

J. McBride

184
6

It's not a matter of Django but MySQL. – nik_m Mar 21 '17 at 18:06
Is this unique to specific MySQL releases? This works fine for me with MySQL 5.5. – J. McBride Mar 21 '17 at 18:09
I am not aware of which version but sure is a `MySQL` issue. – nik_m Mar 21 '17 at 18:11
I agree. I guess we need more information about OP's MySQL setup. It's a little suspect that his single user example is working, but grabbing from multiple users is giving him trouble. I wonder how different the actual queries are. – J. McBride Mar 21 '17 at 18:13
Edited the question to include the mysql and django version. I am using MySQL 5.7.12 and Django 1.8 – Zishan Ahmad Mar 21 '17 at 18:20
@J.McBride list_id is just like you gave in the example, its a list: [1,2,3]. Also, if I do an update like you did: update(first_name='Test'), then that works fine. it's only when using 'Concat' with the value of the field itself that I get this error – Zishan Ahmad Mar 21 '17 at 18:22
@ZishanAhmad What's the query that the ORM generates? You can find it by opening up your shell, importing `django.db.connection` (`from django.db import connection`), executing the statement, and then printing `connection.queries[-1]`. I was able to use `Concat` exactly how you did with a list and had no trouble. – J. McBride Mar 21 '17 at 18:34
@ZishanAhmad Also, if you're just looking to get this done: I still think iterating over the QuerySet and manually updating (i.e. `user.email = 'prefix' + user.email`) and saving is worth doing. You shouldn't be running this query frequently. While it's important to use your database efficiently, it's also important to get work done ;). – J. McBride Mar 21 '17 at 18:42
@J.McBride Here is the formatted output: {u'sql': u"UPDATE `auth_user` SET `email` = CONCAT(COALESCE('prefix+', ''), COALESCE(`auth_user`.`email`, '')) WHERE `auth_user`.`id` IN (SELECT U0.`id` FROM `auth_user` U0 WHERE U0.`email` IN ('email1@xyz.com', 'email2@xyz.com', 'email3@xyz.com', 'email4@xyz.com'))", u'time': u'0.000'} – Zishan Ahmad Mar 21 '17 at 18:46
Interesting. So, you can see there's a subquery being generated (`WHERE 'auth_user.id' IN (SELECT U0.'id' FROM 'auth_user')`). For my query (`User.objects.filter(pk__in=[1, 2]).update(first_name=Concat(Value('user_'), 'first_name'))`), Django didn't generate a subquery. I'm not sure if the ORM got smarter in 1.10 or if there's something radically different about our queries. Can we see the full snippet of code you're executing? – J. McBride Mar 21 '17 at 18:52
@J.McBride I was hoping to save some keystrokes.. but I guess i will have to go the long way. Thanks for your help though! – Zishan Ahmad Mar 21 '17 at 18:53

score 0 · Answer 2 · answered Mar 21 '17 at 18:32

0

This should work :)

User.objects.filter(pk__in=<list_id>).update(email=Concat(Value("prefix"), F('email')))

answered Mar 21 '17 at 18:32

rtindru

4,150
7
32
49

score 0 · Answer 3 · answered Nov 28 '18 at 07:29

I had the same problem, but solved by trying to convert queryset list_ids to a list(), like this:

ids = Transactions.objects.values_list('id', flat=True)
# This may raise error:
User.objects.filter(pk__in=ids).update(first_name='Test')

# But if convert ids to a list, should work:
list_ids = list(ids)
User.objects.filter(pk__in=list_ids).update(first_name='Test')

Hope it helps :)

String concatenation by using the value of the model field while updating a queryset

3 Answers3