使用模型字段的值来连接字符串，同时更新查询

Question

我想把一些用户的email id更新为：“prefix”+“value of the user email”

现在我可以为一个用户执行以下操作：

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

然而，当我在pk列表中过滤时，我得到了一个严重的错误。问题是：

^{pr2}$

错误是：

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

我真的不明白错误信息想要说什么。任何帮助都将不胜感激。在

编辑： MySQL版本：5.7.12 丹戈：1.8

Answer 1

我也遇到了同样的问题，但通过尝试将queryset list_id转换为list（）来解决，如下所示：

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

希望有帮助：）

Answer 2

你的<list_id>长什么样？User.objects.filter(pk__in=<list_id>的结果是什么？在

在Django 1.10中，我可以运行：

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

至于解决方法，显然它没有那么高效，但是为什么不缓存查询集，然后迭代它来更新每个对象呢？这是一个一次性的查询，所以我不确定SQL效率有多大。在

Answer 3

这应该行得通：）

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