Mysql update多表更新即使用-蒲公英云

Mysql update多表更新即使用

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

fund_code	fund_manager_code
123450	dk_001,dk_230,dk_589
011455	dk_011,dk_589

关系数据移动到关系表—fund_product_manager_relation

fund_code	fund_manager_code
123450	dk_001
123450	dk_230
123450	dk_589
011455	dk_011
011455	dk_589

-- 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)使用！

select SUBSTRING(“123.456.78”,-2,3)	78
select SUBSTRING(“123.456.78”,2,3)	23.
select substring_index(“123.456.78”,’.’,3)	123.456.78
select substring_index(“123.456.78”,’.’,1) 正数从左往右数分隔符，取分隔符左侧内容	123
select substring_index(“123.456.78”,’.’,-1) 从从右往左数分隔符，取分隔符右侧内容	78