mysql> update student t1, student t2
-> set t1.gpa = t2.gpa
-> where t1.id=5 and t2.id=1;
Query OK, 1 row affected (0.36 sec)
Rows matched: 1 Changed: 1 Warnings: 0
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 | 4.0 |
+----+--------+------+
5 rows in set (0.00 sec)
mysql>
On Thu, Aug 23, 2012 at 2:47 PM, Elim Qiu <elim....@gmail.com> wrote:
> 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>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
