MySQL数据表处理需求：批量更新及重命名重复记录

Got it, let's tackle your two MySQL tasks clearly and safely—always good to verify before updating to avoid accidental data changes!

Task 1: Batch Deactivate Unused Duplicate Books #

Your existing update statement is on the right track, but first, always run a SELECT to confirm which records will be modified to prevent mistakes:

-- Verify the records that will be deactivated first
SELECT * FROM books 
WHERE book_name IN (SELECT book_name FROM books GROUP BY book_name HAVING COUNT(1) > 1) 
AND is_inuse != 1;

Once you're confident the results are correct, execute your update:

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;

This targets all duplicate book names, filters out any that are currently in use (is_inuse != 1), and marks them as inactive (is_active = 0).

Task 2: Rename Duplicate Books (Excluding the One with the Smallest book_id) #

To rename duplicates by prepending their book_id to the title (keeping the record with the smallest book_id unchanged), we'll use a subquery to identify the "original" record per book name, then join it back to the main table for the update:

First, verify the new names before applying changes:

-- Preview the new book names
SELECT 
  b.book_id, 
  b.book_name, 
  CONCAT(b.book_id, ' - ', b.book_name) AS new_book_name
FROM books b
JOIN (
  -- Get the smallest book_id for each duplicate book name
  SELECT book_name, MIN(book_id) AS min_book_id
  FROM books
  GROUP BY book_name
  HAVING COUNT(1) > 1
) AS duplicate_groups ON b.book_name = duplicate_groups.book_name
WHERE b.book_id != duplicate_groups.min_book_id;

If the preview looks right, run the update:

UPDATE books b
JOIN (
  SELECT book_name, MIN(book_id) AS min_book_id
  FROM books
  GROUP BY book_name
  HAVING COUNT(1) > 1
) AS duplicate_groups ON b.book_name = duplicate_groups.book_name
SET b.book_name = CONCAT(b.book_id, ' - ', b.book_name)
WHERE b.book_id != duplicate_groups.min_book_id;

Quick Notes: #

• The CONCAT(b.book_id, ' - ', b.book_name) adds a separator between the ID and title—feel free to adjust the ' - ' part to whatever format you prefer (like '_' or ' | ').
• This ensures only duplicate records (not the original one with the smallest ID) get renamed, keeping your "primary" copy of each book intact.

内容的提问来源于stack exchange，提问作者Ahmad Qureshi