Consider two rows with id m and n in a table tb with a column cl,
what's the sql command to set the value of cl in row n the same as
that in row m?
Below is my testing
mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
| gpa | decimal(2,1) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> select * from student;
+----+--------+------+
| id | name | gpa |
+----+--------+------+
| 1 | Bob | 4.0 |
| 2 | Ming | 3.8 |
| 3 | Ann | 3.8 |
| 4 | Howe | 3.8 |
| 5 | nobody | 3.8 |
+----+--------+------+
5 rows in set (0.00 sec)
mysql> update student set gpa = (select gpa from student where id=1) where id=5;
ERROR 1093 (HY000): You can't specify target table 'student' for
update in FROM clause
mysql>
