mysql update 子查询

select max(id) as id from djgl_dzqt_replace_audit where audit_genre = 3  and audit_result = 0  and state = 0 and business_id = 2;
SELECT * from  djgl_dzqt_replace_audit WHERE id= 27;
UPDATE djgl_dzqt_replace_audit a ,(select max(id) as id from djgl_dzqt_replace_audit where audit_genre = 3  and  audit_result = 0  and state = 0 and business_id = 2 ) b  set a.STATE=-1 where a.id=b.id;

 

-----------------------------------------------------------------------------------------

mysql> update tb a,
    -> (select SEC_TO_TIME(sum(TIME_TO_SEC(time3))) col,max(time1) time,name
    -> from tb group by DATE_FORMAT(time1, '%Y-%m-%d'))b
    -> set time4=b.col
    -> where a.name=b.name and a.time1=b.time;

-----------------------------------------------------------------------------------------

 

UPDATE test.tb_vobile a

join

(select max(id) id from test.tb_vobile) b

on a.id = b.id

set a.name = '123 ' ;

或

 

UPDATE test.tb_vobile a ,(select max(id) id from test.tb_vobile) b

set a.name = '321 '

WHERE

a.id = b.id ;

-----------------------------------------------------------------------------------------

 

 

 

关联子查询Update语句更新数据

--方法1：

Update  Table1  set  c = ( select  c  from  Table2  where  a = Table1.a)  where  c  is  null 

 

--方法2：

update   A

set   newqiantity=B.qiantity

from   A,B

where   A.bnum=B.bnum 

 

--方法3：

update

     ( select  A.bnum ,A.newqiantity,B.qiantity  from  A  left  join  B  on  A.bnum=B.bnum)  AS  C

set  C.newqiantity = C.qiantity

where  C.bnum =XX 

 

#子查询（不支持）
update question q set q.`level`=2 where q.id in( select id from  question where id>=2111 limit 165,165);

#改写
update question q inner join ( select id from  question where id>=2111 limit 165,165) t on q.id=t.id set q.`level`=2;

#子查询（不支持）
delete from `user` where id in (
   select min(id) as id from `user` group by wx_open_id having count(1)>1
);

#改写
delete from `user` where id in (
    select id from(    
       select min(id) as id from `user` group by wx_open_id having count(1)>1
    ) t
);