mysql java jdbc 如何 update select

2019年8月6日17:28:07

sql 不知道怎么写，也没去查，因为需求可能中途需要修改值，有点麻烦

直接用jdbc实现。

查询出来的值，直接根据update条件更新，写在一个方法里

public static void GetWeiLiaoMsg(String day) {try {conn = MysqlJdbc_sys_master.getConnection();String sql = "select a.city,a.n1,b.n2,c.n3 from (select city,sum(msg_count)as n1 from weiliao_msg WHERE push_time like '"+day+"%' group by city )a left join(select city,count(passenger_phone)as n2 from weiliao_yuyue WHERE push_time like '"+day+"%' group by city )b on a.city = b.city left join (select city,count(phone)as n3 from weiliao_phone WHERE push_time like '"+day+"%' group by city)c on a.city = c.city";stmt = conn.prepareStatement(sql);rs = stmt.executeQuery();conn.setAutoCommit(false);String sql_update = "update danke_FpResult set msg = ?,liudian=?,yuyue=? where city = ? and tag = 'allpush' and day = '"+day.replace("-", "")+"'";stmt = conn.prepareStatement(sql_update);while(rs.next()){stmt.setInt(1, rs.getInt(2));stmt.setInt(2, rs.getInt(3));stmt.setInt(3, rs.getInt(4));stmt.setString(4, rs.getString(1));stmt.addBatch();}stmt.executeBatch();conn.commit();System.out.println(new Date()+" insert into danke_FpResult all msg over!!");} catch (SQLException e) {// TODO Auto-generated catch block
            e.printStackTrace();} finally {try {stmt.close();conn.close();} catch (SQLException e) {// TODO Auto-generated catch block
                e.printStackTrace();}}}