查询源表的特定数据，将结果作为新表更新到目标表中

28岁青春痘老男孩

已于 2022-08-29 09:58:20 修改

阅读量503

点赞数

文章标签： java servlet 前端数据库 sql

于 2022-08-27 10:54:48 首次发布

本文链接：https://blog.csdn.net/stan_ko/article/details/126555453

版权

<update id="updateMemberAdd">
        UPDATE member.es_member m ,

            (SELECT ship_province_id,ship_city_id,ship_county_id,ship_town_id,ship_province,ship_city,ship_county,ship_town,member_id FROM trade.es_order o

            where
                <if test="memberId != null and memberId != ''">
                    o.member_id = #{memberId}
                </if>
            GROUP BY ship_province

            order by count(ship_province) desc

            LIMIT  1) res

        set m.province_id=res.ship_province_id, m.city_id=res.ship_city_id, m.county_id=res.ship_county_id, m.town_id=res.ship_town_id, m.province=res.ship_province, m.city=res.ship_city, m.county=res.ship_county, m.town=res.ship_town

        WHERE res.member_id = m.member_id
    </update>

第一次在mapper里写update：将用户购买最多使用的地址更新到member表里，这里是把子查询结果当作一张新表res，将res拿来更新m表，要注意的是语法竟然是”update表1，表2...“，这里的逗号不能漏掉，这里底层的逻辑是拿逗号分割的吗

代码报错了：

2022-08-29 09:46:02,641 ERROR [http-nio-8005-exec-2] org.apache.catalina.core.ContainerBase.[Tomcat].[localhost].[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: Error preparing statement.  Cause: java.lang.ClassCastException: org.apache.shardingsphere.sql.parser.sql.segment.dml.column.ColumnSegment cannot be cast to org.apache.shardingsphere.sql.parser.sql.segment.dml.expr.ExpressionSegment] with root cause
java.lang.ClassCastException: org.apache.shardingsphere.sql.parser.sql.segment.dml.column.ColumnSegment cannot be cast to org.apache.shardingsphere.sql.parser.sql.segment.dml.expr.ExpressionSegment

这句sql在mysql里写就没问题，但是用mybatis出现问题