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
);