Django update() to swap values of two fields in MySQL?
<p>The following does not work:</p>

<pre><code>Car.objects.filters(&lt;filter&gt;).update(x=F('y'), y=F('x'))
</code></pre>

<p>as both <code>x</code> and <code>y</code> ends up being the same value. </p>

<p>I need to use update() instead of save() due to performance (large set of records).</p>

<p>Are there any other way of doing an update like the one above to mimic Python's <code>x, y = y, x</code>?</p>

<p>The db is MySQL, <a href="http://dba.stackexchange.com/questions/31045/swap-columns-on-some-rows/31052#31052">which might explain why the resulting SQL statement doesn't work</a>.</p>

<p>This should work correctly if you're using a proper standard-compliant SQL database. The query would expand to</p>

<pre><code>UPDATE car SET x = y, y = x WHERE &lt;filter&gt;
</code></pre>

<p>It would work correctly at least on <a href="http://stackoverflow.com/a/37660/918959">PostgreSQL</a> (also below), SQLite3 (below), <a href="http://stackoverflow.com/questions/37649/swapping-column-values-in-mysql#comment17537164_37660">Oracle</a> and <a href="http://stackoverflow.com/questions/4198587/how-do-i-swap-column-values-in-sql-server-2008">MSSQL</a>, however the MySQL implementation is broken.</p>

<p>PostgreSQL:</p>

<pre><code>select * from car;
    x    |  y   
---------+------
 prefect | ford
(1 row)

test=&gt; update car set x = y, y = x;
UPDATE 1
test=&gt; select * from car;
  x   |    y    
------+---------
 ford | prefect
(1 row)
</code></pre>

<p>SQLite3</p>

<pre><code>sqlite&gt; select * from foo;
prefect|ford
sqlite&gt; update foo set x = y, y = x;
sqlite&gt; select * from foo;
ford|prefect
</code></pre>

<p>However, MySQL violates the SQL standard,</p>

<pre><code>mysql&gt; insert into car values ('prefect', 'ford');
Query OK, 1 row affected (0.01 sec)

mysql&gt; select * from car;
+---------+------+
| x       | y    |
+---------+------+
| prefect | ford |
+---------+------+
1 row in set (0.00 sec)

mysql&gt; update car set x = y, y = x;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql&gt; select * from car;
+------+------+
| x    | y    |
+------+------+
| ford | ford |
+------+------+
1 row in set (0.00 sec)
</code></pre>

<p>Thus there is a portable way to do this using a standard-compliant SQL database, but MySQL isn't one of them. If you cannot use the <code>for</code> ... <code>save</code> loop, then you must resort to some of the hacks from <a href="http://stackoverflow.com/questions/37649/swapping-column-values-in-mysql">Swapping column vales in MySQL</a>; the temporary variable seems to be the most generic one; though I am not sure whether you can use the temporary variables with <code>F</code> constructs of Django.</p>

python mysql django