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