List:General Discussion« Previous MessageNext Message »
From:Elim Qiu Date:August 23 2012 8:47pm
Subject:copy some values between rows
View as plain text  
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>
Thread
copy some values between rowsElim Qiu23 Aug
  • Re: copy some values between rowsElim Qiu23 Aug