MySQL一条语句实现同时查询和修改

在工作中遇到一件事情，现在有3个表A（结算单信息表），B（结算单对应的流水表），C（结算单和流水的对应关系表）

现在要根据A表中的结算单编号bill_number 去修改B表中流水的结算主体的名称

这个时候就需要先查询出结算单对应个流水信息然后在更新了

不说了经过多次试验终于搞定

先写查询语句

select sfpi.id, bill_number,merchant_name from  B as sfpi  LEFT JOIN C as bfr ON bfr.flow_id=sfpi.id LEFT JOIN A as sbi ON bfr.bill_id=sbi.id where bill_number="11"

再写更新语句

UPDATE B as sfp set sfp.merchant_name ="测试名称"   //(当然这是不对的，我们要把上面查询的语句结果当成更新条件)

将查询语句当成更新语句的条件

UPDATE settle_flow_pay_info as sfp INNER JOIN (

select sfpi.id, bill_number,merchant_name from settle_flow_pay_info as sfpi

LEFT JOIN bill_flow_relation as bfr ON bfr.flow_id=sfpi.id

LEFT JOIN settle_bill_info as sbi ON bfr.bill_id=sbi.id

where bill_number in ("11","13",'12')) as select_result set sfp.`merchant_name` = "****限公司",sfp.`merchant_id` = "7",sfp.`merchant_local_id` = "157" where sfp.id in (select_result.id)

完成了！你也来试试吧！是不是很简单！