mysql的delete from tab1 where id in(select id from tab2)这里的tab1与tab2不能为同一张表,如是都为表tab1则会报错:
You can’t specify target table ’tab1’ for update in FROM clause (不能更新子查询里的指定表)
解决方法:1.建立临时表
create table tmp as select pid as pid from tab1 group by pid having count(1)>1;’根据自己业务写建表SQL
delete from pid where pid in (select pid from tmp);
drop table tmp;
delete一张表与不同表写法的区别:
delete ability_score from ability_score join score on score.id=ability_score.user_score_id
where score.activity_id=#{activityId} and score.grade_id=#{gradeId}
delete from score
where score.activity_id=#{activityId} and score.grade_id=#{gradeId}
2.update值来自另一张表
update char_dict inner join
char_struct set char_dict.struct_id = char_struct.id
where char_dict.struct_name = char_struct.struct_name ;
update base_small_pic inner join
base_big_pic on base_small_pic.big_pic_id=base_big_pic.id
inner join biz_import_batch on biz_import_batch.batch_seq=base_big_pic.import_seq
set base_small_pic.batch_id= biz_import_batch.id;
where base_small_pic.batch_id is null
update同一张表:
update platform_city c1 inner join
platform_city c2 on c1.code=c2.parent_code
set c2.parent_id=c1.id;
3.insert from,生成id
insert into new_tab(id,tbook_id,unit_id,chapter_id,name,type,is_delete,create_time)
select (@rownum := @rownum + 1)+100000 AS rownum,tbook_id,tbook_unit_id,tbook_chapter_id, 'xx', '5', '0' ,now()
from old_tab biz_tbook_chapter,
(SELECT @rownum := 0) AS rn
where tbook_chapter_id is not null;
更多实用SQL,可参考:
评论:
提交