duandang6111
2016-02-19 10:47
浏览 245
已采纳

Mysql使用匹配值插入或更新一个表列数据到另一个表

Iam bit new to Mysql, What is my question is, how to Update or Insert Table_1 column data to Table_2 column data with matching the relevant Id's from Table_1.

For Eg.

          Table_1                       Table_2                      

        rand  | cpn                   rand   |  cpn     
         1        4                     2        0
         2        7                     3        0
         3        2                     5        0
         4        1                     1        0
         5        7                     4        0

The above mentioned table are the current structure of the my tables, my output should be like this,

      Table_2

   rand   |  cpn     
     2        7
     3        2
     5        7
     1        4
     4        1 

I want to match the ids from table_1 and update in table_2, accordingly Thanks in Advance :)

图片转代码服务由CSDN问答提供 功能建议

我是Mysql的新手,我的问题是,如何更新或插入 Table_1 列数据到 Table_2 列数据,与 Table_1 中的相关ID匹配。

对于例如。

  Table_1 Table_2 
 
 rand |  cpn rand |  cpn 
 1 4 2 0 
 2 7 3 0 
 3 2 5 0 
 4 1 1 0 
 5 7 4 0 
   
 
 

以上 提到的表是我的表的当前结构,我的输出应该是这样的,

  Table_2 
 
 rand |  cpn 
 2 7 
 3 2 
 5 7 
 1 4 
 4 1 
   
 
 

我想匹配 table_1 <中的ID / strong>并在 table_2 中更新,相应提前感谢:)

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • douya1061 2016-02-19 10:54
    已采纳

    In mysql you can use joins in updates to achive the expected results:

    update table2 t2 inner join table1 t1 on t2.rand=t1.rand
    set t2.cpn=t1.cpn 
    
    点赞 评论
  • dongxidui1227 2016-02-19 10:53

    Well, you should JOIN the tables so you can use the values, like so:

    UPDATE Table_2 t2 
    LEFT JOIN Table_1 t1 on t2.rand = t1.rand
    SET t2.cpn = t1.cpn;
    
    点赞 评论
  • douchongbang6011 2016-02-19 10:58

    This should work:

    update table2 t2 set cpn = (select cpn from table1 where rand = t2.rand)
    
    点赞 评论
  • dta25920 2016-02-19 11:01

    You can use this. it INSERT OR UPDATE Records. You only must have a unique INDEX or PRIMARY KEY on rand.

    INSERT INTO Table_2
     (SELECT * FROM Table_1 )
     ON DUPLICATE KEY UPDATE cpn = VALUES(cpn);
    

    SAMPLE

    MariaDB []>
    MariaDB []> CREATE TABLE `Table_1` (
        ->   `rand` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        ->   `cpn` INT(11) DEFAULT NULL,
        ->   PRIMARY KEY (`rand`)
        -> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
    Query OK, 0 rows affected (0.16 sec)
    
    MariaDB []>
    MariaDB []> INSERT INTO `Table_1` (`rand`, `cpn`)
        -> VALUES
        -> (1, 4),
        -> (2, 7),
        -> (3, 2),
        -> (4, 1),
        -> (5, 7);
    Query OK, 5 rows affected (0.02 sec)
    Records: 5  Duplicates: 0  Warnings: 0
    
    MariaDB []>
    MariaDB []>
    MariaDB []> CREATE TABLE `Table_2` (
        ->   `rand` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        ->   `cpn` INT(11) DEFAULT NULL,
        ->   PRIMARY KEY (`rand`)
        -> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
    Query OK, 0 rows affected (0.30 sec)
    
    MariaDB []>
    MariaDB []> INSERT INTO `Table_2` (`rand`, `cpn`)
        -> VALUES
        -> (1, 0),
        -> (2, 0),
        -> (3, 0),
        -> (4, 0),
        -> (5, 0);
    Query OK, 5 rows affected (0.00 sec)
    Records: 5  Duplicates: 0  Warnings: 0
    
    MariaDB []>
    MariaDB []>
    MariaDB []> SELECT * FROM Table_1;
    +------+------+
    | rand | cpn  |
    +------+------+
    |    1 |    4 |
    |    2 |    7 |
    |    3 |    2 |
    |    4 |    1 |
    |    5 |    7 |
    +------+------+
    5 rows in set (0.00 sec)
    
    MariaDB []>
    MariaDB []> SELECT * FROM Table_2;
    +------+------+
    | rand | cpn  |
    +------+------+
    |    1 |    0 |
    |    2 |    0 |
    |    3 |    0 |
    |    4 |    0 |
    |    5 |    0 |
    +------+------+
    5 rows in set (0.00 sec)
    
    MariaDB []>
    MariaDB []> INSERT INTO Table_2
        ->  (SELECT * FROM Table_1 )
        ->  ON DUPLICATE KEY UPDATE cpn = VALUES(cpn);
    Query OK, 10 rows affected (0.00 sec)
    Records: 5  Duplicates: 5  Warnings: 0
    
    MariaDB []>
    MariaDB []> SELECT * FROM Table_2;
    +------+------+
    | rand | cpn  |
    +------+------+
    |    1 |    4 |
    |    2 |    7 |
    |    3 |    2 |
    |    4 |    1 |
    |    5 |    7 |
    +------+------+
    5 rows in set (0.00 sec)
    
    MariaDB []>
    
    点赞 评论

相关推荐 更多相似问题