使用mysql 自带的语句构建批量更新

最新推荐文章于 2023-03-06 09:31:04 发布

临海听风V

最新推荐文章于 2023-03-06 09:31:04 发布

阅读量632

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL

本文链接：https://blog.youkuaiyun.com/samuelandkevin/article/details/78331890

SQL 专栏收录该内容

4 篇文章

订阅专栏

本文介绍MySQL中批量更新数据的方法，包括使用CASE语句更新多条记录，并展示了如何结合多个表进行更新操作。此外还提供了查询订单记录及数据迁移的具体SQL语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

多条语句更新执行如：

UPDATE `itop233`.`priv_change` SET `date`='2015-07-31 08:08:32' WHERE `id`='33';
UPDATE `itop233`.`priv_change` SET `date`='2015-07-31 08:09:18' WHERE `id`='34';
UPDATE `itop233`.`priv_change` SET `date`='2015-07-31 08:10:43' WHERE `id`='35';
UPDATE `itop233`.`priv_change` SET `date`='2015-07-31 08:12:11' WHERE `id`='37';
UPDATE `itop233`.`priv_change` SET `date`='2015-10-11 09:28:00' WHERE `id`='69';
UPDATE `itop233`.`priv_change` SET `date`='2015-10-11 09:38:47' WHERE `id`='72';
UPDATE `itop233`.`priv_change` SET `date`='2015-10-12 08:07:45' WHERE `id`='82';
UPDATE `itop233`.`priv_change` SET `date`='2015-10-12 08:08:48' WHERE `id`='83';

MySql批量更新：

        UPDATE yoiurtable
        SET dingdan = CASE id 
            WHEN 1 THEN 3 
            WHEN 2 THEN 4 
            WHEN 3 THEN 5 
        END
    WHERE id IN (1,2,3)
    这句sql 的意思是，更新dingdan 字段，如果id=1 则dingdan 的值为3，如果id=2 则dingdan 的值为4……
    where部分不影响代码的执行，但是会提高sql执行的效率。确保sql语句仅执行需要修改的行数，这里只有3条数据进行更新，而where子句确保只有3行数据执行。   
    例子：UPDATE book
        SET Author = CASE id 
            WHEN 1 THEN '黄飞鸿' 
            WHEN 2 THEN '方世玉'
            WHEN 3 THEN '洪熙官'
        END
    WHERE id IN (1,2,3)
    如果更新多个值的话，只需要稍加修改：
    UPDATE categories      
        SET dingdan = CASE id 
            WHEN 1 THEN 3 
            WHEN 2 THEN 4 
            WHEN 3 THEN 5 
        END, 
        title = CASE id 
            WHEN 1 THEN 'New Title 1'
            WHEN 2 THEN 'New Title 2'
            WHEN 3 THEN 'New Title 3'
        END
    WHERE id IN (1,2,3)
   例子：UPDATE book
        SET Author = CASE id 
            WHEN 1 THEN '黄飞鸿2' 
            WHEN 2 THEN '方世玉2'
            WHEN 3 THEN '洪熙官2'
        END,
        Code = CASE id 
            WHEN 1 THEN 'HFH2' 
            WHEN 2 THEN 'FSY2'
            WHEN 3 THEN 'HXG2'
        END
    WHERE id IN (1,2,3)**

到这里，已经完成一条mysql语句更新多条记录了。

好了，很有用，记录下来，具体的Book的表结构不写了，大家可以建立自己的表结构，字段改成自己的字段就可以了。MySql是挺好用的。

参考:
大批量更新数据mysql批量更新的四种方法

#='001e92d8-06cb-4035-8554-ebe548b3172b'
SELECT 
  *
FROM
  info_orders io 
  INNER JOIN 
    (SELECT DISTINCT 
      id 
    FROM
      (SELECT 
        io.id 
      FROM
        info_orders io,
        info_order_details iod 
        INNER JOIN 
          (SELECT 
            ic.id 
          FROM
            info_case ic,
            user_store us 
          WHERE ic.`create_man` = us.`user_id` 
            AND us.`user_id` =@user_id) e 
          ON iod.`product_id` = e.id 
      WHERE io.`order_no` = iod.`order_no` 
        AND io.`order_product_type` = 1 
      UNION
      ALL 
      SELECT 
        io.id 
      FROM
        info_orders io,
        info_order_details iod 
        INNER JOIN 
          (SELECT 
            ic.id 
          FROM
            info_case ic,
            user_store us 
          WHERE ic.`create_man` = us.`user_id` 
            AND us.`user_id` = @user_id) e 
          ON iod.`product_id` = e.id 
      WHERE io.`order_no` = iod.`order_no` 
        AND io.`order_product_type` = 2 
      UNION
      ALL 
      SELECT 
        io.id 
      FROM
        info_orders io,
        info_order_details iod 
        INNER JOIN 
          (SELECT 
            ic.id 
          FROM
            info_case ic,
            user_store us 
          WHERE ic.`create_man` = us.`user_id` 
            AND us.`user_id` = @user_id) e 
          ON iod.`product_id` = e.id 
      WHERE io.`order_no` = iod.`order_no` 
        AND io.`order_product_type` = 3) e) e 
    ON e.id = io.`id` 
  LEFT JOIN info_order_details iod 
    ON io.`order_no` = iod.`order_no`

DCLARE @user_id VARCHAR;
set @user_id = '322d01ef-8a7a-4984-94f0-c383b014c1ec';

select * from info_order_details iod
WHERE iod.product_id IN(

SELECT  ic.id  FROM  info_case ic,user_store us WHERE ic.`create_man` = us.`user_id` 
AND us.`user_id` =@user_id 

UNION  

SELECT tgb.id FROM tax_goods_books tgb,user_store us WHERE tgb.`user_id` = us.`user_id` 
AND us.`user_id` = @user_id 

UNION      

SELECT tgb.id FROM tax_goods_base tgb, user_store us WHERE tgb.`user_id` = us.`user_id` 
AND us.`user_id` = @user_id


) ORDER BY iod.create_time desc;

查询订单记录

SELECT
    a.order_no AS '订单号',
    b.product_id AS '商品id',
    (
        CASE a.product_type
        WHEN 1 THEN
            e.`name`
        WHEN 3 THEN
            d.title
        END
    ) AS '商品名',
    b.quantity AS '数量',
    a.money AS '订单金额',
    (
        CASE a.order_type
        WHEN 1 THEN
            '在线支付订单'
        WHEN 2 THEN
            '电话订购订单'
        END
    ) '订单类型',
    (
        CASE a.product_type
        WHEN 1 THEN
            '工作指引'
        WHEN 2 THEN
            '培训视频'
        WHEN 3 THEN
            '案例'
        END
    ) '商品类型',
    (
        CASE a. STATUS
        WHEN 0 THEN
            '待支付'
        WHEN 1 THEN
            '已支付'
        WHEN 2 THEN
            '已完成'
        WHEN 3 THEN
            '已取消'
        WHEN 4 THEN
            '已删除'
        END
    ) '订单状态',
    (
        CASE a.payment_type
        WHEN 1 THEN
            '微信支付'
        WHEN 2 THEN
            '支付宝END'
        ELSE
            ''
        END
    ) AS '支付方式',
    a.user_id AS '用户id',
    c.user_name AS '用户名',
    c.mobile AS '手机号',
    (
        CASE c.gender
        WHEN 1 THEN
            '男'
        WHEN - 1 THEN
            '女'
        ELSE
            ''
        END
    ) AS '性别',
    a.payment_time AS '支付时间',
    a.created_date AS '创建时间',
    a.updated_date AS '更新时间'
FROM
    taxtao_user_order a
LEFT JOIN taxtao_user_order_detail b ON a.id = b.order_id
LEFT JOIN taxtao_user_account c ON a.user_id = c.id
LEFT JOIN taxdb.info_case d ON d.id = b.product_id
AND a.product_type = 3
LEFT JOIN taxtao_book_base_info e ON e.id = b.product_id
AND a.product_type = 1
WHERE
    a.order_type = 1
ORDER BY
    a.updated_date DESC;

把表fl_tax_law_lib_1的数据插进表flysoft：

INSERT INTO flysoft (
    oldID,
    SF_TYPE,
    NAME,
    WENHAO,
    CONTENT,
    ISSUE_DATE,
    INVALIDDATE,
    WHERE_ISSUE,
    TAX_TYPE,
    BUSINESS_TYPE,
    MODIFYTIME,
    URL,
    id,
    is_no,
    update_time,

) SELECT
    fl_no,
    sf_type,
    title,
    wenhao,
    content,
    issue_date,
    invalid_date,
    where_issue,
    tax_type,
    business_type,
    modify_time,
    linkurl,
    mark,
    0,
    now()
FROM
    fl_tax_law_lib_1

更新多个字段 update select inner join

在遇到需要update设置的参数来自从其他表select出的结果时,需要把update和select结合使用,不同数据库支持的形式不一样,在mysql中如下:

update A inner join(select id,name from B) c on A.id = c.id set A.name = c.name;

根据AB两个表的id相同为条件,把A表的name修改为B的sql语句就如上所示