The following does not work:
Car.objects.filters(<filter>).update(x=F('y'), y=F('x'))
as both x and y ends up being the same value.
I need to use update() instead of save() due to performance (large set of records).
Are there any other way of doing an update like the one above to mimic Python's x, y = y, x?
The db is MySQL, which might explain why the resulting SQL statement doesn't work.
This should work correctly if you're using a proper standard-compliant SQL database. The query would expand to
UPDATE car SET x = y, y = x WHERE <filter>
It would work correctly at least on PostgreSQL (also below), SQLite3 (below), Oracle and MSSQL, however the MySQL implementation is broken.
PostgreSQL:
select * from car;
x | y
---------+------
prefect | ford
(1 row)
test=> update car set x = y, y = x;
UPDATE 1
test=> select * from car;
x | y
------+---------
ford | prefect
(1 row)
SQLite3
sqlite> select * from foo;
prefect|ford
sqlite> update foo set x = y, y = x;
sqlite> select * from foo;
ford|prefect
However, MySQL violates the SQL standard,
mysql> insert into car values ('prefect', 'ford');
Query OK, 1 row affected (0.01 sec)
mysql> select * from car;
+---------+------+
| x | y |
+---------+------+
| prefect | ford |
+---------+------+
1 row in set (0.00 sec)
mysql> update car set x = y, y = x;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from car;
+------+------+
| x | y |
+------+------+
| ford | ford |
+------+------+
1 row in set (0.00 sec)
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 for ... save loop, then you must resort to some of the hacks from Swapping column vales in MySQL; the temporary variable seems to be the most generic one; though I am not sure whether you can use the temporary variables with F constructs of Django.
I don't think update can do that as it's basically an sql wrapper. What you could do, though, is use save(values=['x','y']). Hopefully it won't be as slow. Alternatively, you could use raw sql from django to perform the swap (see Swap values for two rows in the same table in SQL Server)
