Mysql update多表更新即使用

我会带着你远行 2023-10-14 17:08 8阅读 0赞

>     1. inner join
>     UPDATE topic_relation r
>     INNER JOIN course c ON c.id = r.target_id
>     SET r.target_code = c.course_code
>     WHERE r.type = '1012005';
> 
> 2.隐式
> 
>     update test_user set login_code=SUBSTRING_INDEX(email,'@',1);
>     update province_channel_manager as a,test_user as b set a.login_code = b.login_code 
>     where a.channel_manager_num=b.telephone;
> 
> 3.left join 此时product 中比 product\_price多一条数据，该数据就是修改的数据
> 
>     UPDATE product p LEFT JOIN product_price pp ON p.productid= pp.productid SET p.isdelete = 1 WHERE pp.productid IS NULL;
>     Query OK, 1 row affected (0.04 sec)
> 
> 4.子查询
> 
>     UPDATE product_price pp SET price=(SELECT price*0.8 FROM product WHERE productid = pp.productid);
> 
> 5.同时更新多个字段
> 
> UPDATE product p INNER JOIN product\_price pp ON p.productid= pp.productid SET pp.price = p.price \* 0.8, p.dateUpdate = CURDATE()

>     给表同时添加多个字段
>     ALTER TABLE xxName
>     ADD COLUMN `xx_id` BIGINT DEFAULT NULL COMMENT 'xxid' AFTER `status`,
>     ADD COLUMN `info_id` BIGINT DEFAULT NULL COMMENT 'info信息id' AFTER `xx_id`;

原表—fund\_product\_info

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>fund_code</td> 
   <td>fund_manager_code</td> 
  </tr> 
  <tr> 
   <td>123450</td> 
   <td>dk_001,dk_230,dk_589</td> 
  </tr> 
  <tr> 
   <td>011455</td> 
   <td>dk_011,dk_589</td> 
  </tr> 
 </tbody> 
</table>

关系数据移动到关系表—fund\_product\_manager\_relation

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>fund_code</td> 
   <td>fund_manager_code</td> 
  </tr> 
  <tr> 
   <td>123450</td> 
   <td>dk_001</td> 
  </tr> 
  <tr> 
   <td>123450</td> 
   <td>dk_230</td> 
  </tr> 
  <tr> 
   <td>123450</td> 
   <td>dk_589</td> 
  </tr> 
  <tr> 
   <td>011455</td> 
   <td>dk_011</td> 
  </tr> 
  <tr> 
   <td>011455</td> 
   <td>dk_589</td> 
  </tr> 
 </tbody> 
</table>

-- 1
    drop table  if exists test1;
    CREATE TABLE if not exists `test1` (
      `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
      sort tinyint default null comment'对比id',
      PRIMARY KEY (`id`)
    	) ENGINE=InnoDB COMMENT='协助迁移产品数据';
    INSERT INTO `test1`(`id`, `sort`) VALUES (1, 0);
    INSERT INTO `test1`(`id`, `sort`) VALUES (2, 1);
    INSERT INTO `test1`(`id`, `sort`) VALUES (3, 2);
    INSERT INTO `test1`(`id`, `sort`) VALUES (4, 3);
    
    -- 2
    truncate table fund_product_manager_relation;
    -- 3
    insert into fund_product_manager_relation(fm_sort,fund_code,fund_name,fund_manager_code,fund_manager_name,current_manage)
    SELECT
    	b.sort,
    	a.fund_code,
    	a.fund_name,
    	SUBSTRING_INDEX( SUBSTRING_INDEX( a.fund_manager_code, '，', b.sort + 1 ), '，',- 1 ) fund_manager_code,
    	'',
    CASE
    		deleted 
    		WHEN 1 THEN
    		'N' ELSE 'Y' 
    	END current_manage 
    FROM
    	fund_product_info AS a
    	LEFT JOIN test1 AS b ON ( char_length( a.fund_manager_code ) - char_length( REPLACE ( a.fund_manager_code, '，', '' ) ) ) + 1 > b.sort 
    WHERE
    	a.fund_manager_code IS NOT NULL;
    -- 4
    update fund_product_manager_relation as a, fund_manager_info as b set a.fund_manager_name= b.fund_manager_name where a.fund_manager_code=b.fund_manager_code;
    -- 5
    drop table test1;

主要 辅助表test1+ substring\_index(str,delim,count)使用！

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td style="width:256px;">select SUBSTRING("123.456.78",-2,3)</td> 
   <td style="width:242px;">78</td> 
  </tr> 
  <tr> 
   <td style="width:256px;">select SUBSTRING("123.456.78",2,3)</td> 
   <td style="width:242px;">23.</td> 
  </tr> 
  <tr> 
   <td style="width:256px;">select substring_index("123.456.78",'.',3)</td> 
   <td style="width:242px;">123.456.78</td> 
  </tr> 
  <tr> 
   <td style="width:256px;">select substring_index("123.456.78",'.',1)<br> 正数从左往右数分隔符，取分隔符左侧内容</td> 
   <td style="width:242px;">123&nbsp;&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:256px;">select substring_index("123.456.78",'.',-1)<br> 从从右往左数分隔符，取分隔符右侧内容</td> 
   <td style="width:242px;">78</td> 
  </tr> 
 </tbody> 
</table>