mysql - 选择除 minimum 之外的所有重复记录

Question

我在 MySQL 中有一个表，名称如下

我有两件事要处理

1- 停用所有未使用的书籍

isActive = 1 - Active
isActive = 0  - Inactive

is_inuse = 1 - in use
is_inuse = 0 - not in use
I have the query as such 

. . .

update books 
set is_active=0 
where book_name in (select (book_name) 
                    from books  
                    group by book_name 
                    having count(1) >1
                   ) and 
      is_inuse != 1;

2 - 重命名除具有最小图书 ID 的图书外的所有重复图书，在图书名称前附加 book_id。努力为这个案例做查询。

Answer 1

您应该能够使用以下 SQL 获取重复书名的列表

select t1.book_id, t1.book_name, concat(t1.book_id, t1.book_name) as new_name 
from table1 t1,
(
select book_name, min(book_id) as book_id from table1
group by book_name
) t2
where t1.book_name = t2.book_name
and t1.book_id != t2.book_id