mysql基础积累-多表关联update

场景:
        多表关联update
环境:
        MySQL 5.5.54 , SQLyog Ultimate 64
1.多表关联update

UPDATE SENSOR_COLLECT_DATA A 
   JOIN SENSOR_COLLECT_A B 
   ON A.SENSOR_ID = B.SENSOR_ID AND A.REGION = B.REGION 
   JOIN SENSOR_COLLECT_B C 
   ON B.SENSOR_ID = C.SENSOR_ID AND B.REGION = C.REGION
   SET A.S1 = B.S1, A.S2 = C.S1
 WHERE A.SENSOR_ID = 20191401
   AND A.REGION = '2019';

2.原始数据截图
  
3.更新后截图
  
4.附建表语句1

CREATE TABLE `sensor_collect_data` (
  `sensor_id` INT(11) DEFAULT NULL,
  `region` VARCHAR(16) DEFAULT NULL,
  `s1` DECIMAL(6,3) DEFAULT NULL,
  `s2` DECIMAL(6,3) DEFAULT NULL,
  `s3` DECIMAL(6,3) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8

5.附建表语句2

CREATE TABLE `sensor_collect_a` (
  `sensor_id` INT(11) DEFAULT NULL,
  `region` VARCHAR(16) DEFAULT NULL,
  `s1` DECIMAL(6,3) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8

6.附建表语句3

CREATE TABLE `sensor_collect_b` (
  `sensor_id` INT(11) DEFAULT NULL,
  `region` VARCHAR(16) DEFAULT NULL,
  `s1` DECIMAL(6,3) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8

7.对比,此语句oracle暂时不支持.
以上,感谢.