Update two table fields, each with the other value

Question

Maybe someone can help me with this.

I have a MYSQL table with first name and last name. Some rows were added with last name in the first name column and first name in the last name column.

How can I flip these. I tried a simple update SET firstname = lastname, lastname = firstname but that doesn't work.

Any help would be great.

Answer 1

mysql> select * from mytable;
+-----------+----------+
| firstname | lastname |
+-----------+----------+
| Potter    | Harry    |
+-----------+----------+

mysql> update mytable 
  set lastname = (@temp := lastname), -- no-op, but sets a variable as a side effect
      lastname = firstname, 
      firstname = @temp;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from mytable;
+-----------+----------+
| firstname | lastname |
+-----------+----------+
| Harry     | Potter   |
+-----------+----------+

Answer 2

Basically you can't do that in a simple update statement in MySQL, because consecutive assignments in the set clause are not independent. This does not work:

update mytable set first_name = last_name, last_name = first_name

When the second assignment is executed, first_name is set to last_name already. Doing so would hence set both columns to last_name.

This is not standard SQL, but that's how MySQL does it. One option uses a self-join. Assuming that the primary key of your table is id:

update mytable t
inner join mytable t1 on t1.id = t.id
set t.first_name = t1.last_name, t.last_name = t1.first_name