mysql batch delete_mysql的delete in同一张表,update值来自另一张表

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，可参考:

评论:

提交