[Mysql语法]--update inner join使用（批量更新）

一：需求

A表和B表的表结构相同，A表是历史表，B表是增量数据表；想要根据关联条件更新A表中的数据。

二：表结构

CREATE TABLE `A` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `bid` bigint(20) NOT NULL ,
  `sid` bigint(20) NOT NULL ,
  `grid` bigint(20) NOT NULL ,
  `age` bigint(20) NOT NULL ,
  `pv` bigint(20) NOT NULL ,
  `uv` bigint(20) NOT NULL ,
  `pay_count` bigint(20) NOT NULL,
  `charge_amount` bigint(20) NOT NULL ,
  `last_pay_count` bigint(20) NOT NULL ,
  `last_charge_amount` bigint(20) NOT NULL ,
  `ftime` bigint(20) NOT NULL ,
  `dtime` bigint(20) NOT NULL DEFAULT '0' COMMENT '详细时间(yyyyMMddHH)'
  PRIMARY KEY (`id`,`ftime`),
  KEY `IX_FTIME` (`ftime`) USING BTREE,
  KEY `IX_HTIME` (`htime`) USING BTREE,
  KEY `IX_DTIME` (`dtime`),
  KEY `IX_B_F_S_G` (`bid`,`ftime`,`sid`,`grid`),
  KEY `IX_B_S_G` (`bid`,`sid`,`grid`),
  KEY `IX_B_D_S` (`bid`,`dtime`,`sid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

注意：mysql 只有2种索引(BTREE 和 HASH)

三：注意

1、只更新A表的部分字段：pv、uv和pay_amount

2、确定唯一一条记录的关联字段是：bid、sid、dtime、qrid

 

四：实现

1、使用inner join实现

UPDATE A as aa
INNER JOIN B as bb ON bb.bid = aa.bid
AND bb.sid=aa.sid
AND bb.grid=aa.grid
SET aa.pv=bb.pv,
    aa.uv=bb.uv,
    aa.pay_amount=bb.pay_amount
WHERE aa.dtime=?
  AND aa.bid=? ;

2、使用exists实现

UPDATE A aa 
SET    aa.pv = (SELECT bb.pv 
                FROM   B bb 
                WHERE  bb.bid = aa.bid 
                       AND bb.sid = aa.sid 
                       AND bb.grid = aa.grid), 
       aa.uv = (SELECT bb.uv 
                FROM   B bb 
                WHERE  bb.bid = aa.bid 
                       AND bb.sid = aa.sid 
                       AND bb.grid = aa.grid), 
       aa.pay_amount = (SELECT bb.pay_amount 
                        FROM   B bb 
                        WHERE  bb.bid = aa.bid 
                               AND bb.sid = aa.sid 
                               AND bb.grid = aa.grid) 
WHERE  EXISTS (SELECT 1 
               FROM   B bb 
               WHERE  bb.bid = aa.bid 
                      AND bb.sid = aa.sid 
                      AND bb.grid = aa.grid) 
       AND aa.dtime = ? 
       AND aa.bid = ?; 

注意：别名中，不要出现mysql的关键字！

五：参考

1、mysql关键字：https://dev.mysql.com/doc/refman/8.0/en/keywords.html

2、update inner join :https://stackoverflow.com/questions/11709043/mysql-update-column-with-value-from-another-table

3、mysql 索引类型：https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html