#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    22
    Rep Power
    0

    A very basic question


    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?

    what's wrong with the following?

    update tb set cl = (select cl from tb where id = m) where id = n;
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,144
    Rep Power
    4274
    Originally Posted by elimqiu
    what's wrong with the following?
    what happened when you tested it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    22
    Rep Power
    0
    Originally Posted by r937
    what happened when you tested it?
    Code:
    mysql> desc student;
    +-------+--------------+------+-----+---------+----------------+
    | Field | Type         | Null | Key | Default | Extra          |
    +-------+--------------+------+-----+---------+----------------+
    | id    | int(11)      | NO   | PRI | NULL    | auto_increment |
    | str   | varchar(36)  | YES  |     | NULL    |                |
    | gpa   | decimal(2,1) | YES  |     | NULL    |                |
    +-------+--------------+------+-----+---------+----------------+
    3 rows in set (0.03 sec)
    
    mysql> select * from student;
    +----+--------+------+
    | id | str    | gpa  |
    +----+--------+------+
    |  1 | Elim   |  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>
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,144
    Rep Power
    4274
    seems like you might have to do it in two steps
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    22
    Rep Power
    0
    The following works for one or two tables:

    Code:
    update table1 t1, table2 t2
    set t2.fld1b = t1.fld1a, ..., t2.fldkb = t1.fldka
    where t1.id = id1 and t2.id = id2;
    In my case:

    Code:
    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.11 sec)
    
    mysql> select * from student;
    +----+--------+------+
    | id | name   | gpa  |
    +----+--------+------+
    |  1 | Elim   |  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 t1, student t2
        -> set t2.gpa = t1.gpa
        -> where t1.id = 1 and t2.id = 5;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from student;
    +----+--------+------+
    | id | name   | gpa  |
    +----+--------+------+
    |  1 | Elim   |  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>

IMN logo majestic logo threadwatch logo seochat tools logo