如何将users2表符合条件的数据迁移至user_totals表？

Got it, let's break down how to handle this migration. The key points here are matching users by ID, only updating target fields when they're 0, and mapping the correct source fields to the target ones.

First, let's start with a validation query to check exactly which records will be affected. This is crucial to avoid unintended changes—always test before running an update!

SELECT 
    u2.id AS source_user_id,
    ut.user_id AS target_user_id,
    u2.clicks AS source_clicks,
    ut.impression_count AS target_impression_count,
    u2.fclicks AS source_fclicks,
    ut.use_count AS target_use_count,
    u2.completed_stus AS source_completed,
    ut.completion_count AS target_completion_count
FROM users2 u2
JOIN user_totals ut ON u2.id = ut.user_id
WHERE 
    ut.impression_count = 0 
    OR ut.use_count = 0 
    OR ut.completion_count = 0;

This query will show you all user pairs where at least one target field is 0, along with the source values you'll be migrating.

Next, the actual update query. We'll use a JOIN to link the two tables, and conditional logic to only update fields that are currently 0:

Standard SQL Version #

UPDATE user_totals ut
SET 
    impression_count = CASE 
        WHEN ut.impression_count = 0 THEN u2.clicks 
        ELSE ut.impression_count 
    END,
    use_count = CASE 
        WHEN ut.use_count = 0 THEN u2.fclicks 
        ELSE ut.use_count 
    END,
    completion_count = CASE 
        WHEN ut.completion_count = 0 THEN u2.completed_stus 
        ELSE ut.completion_count 
    END
FROM users2 u2
WHERE ut.user_id = u2.id;

MySQL-Specific Version #

If you're using MySQL, the syntax for JOINs in UPDATE statements is slightly more concise:

UPDATE user_totals ut
JOIN users2 u2 ON ut.user_id = u2.id
SET 
    impression_count = IF(ut.impression_count = 0, u2.clicks, ut.impression_count),
    use_count = IF(ut.use_count = 0, u2.fclicks, ut.use_count),
    completion_count = IF(ut.completion_count = 0, u2.completed_stus, ut.completion_count);

Key Considerations: #

• Conditional Safety: The CASE/IF statements ensure we never overwrite non-zero values in the target table—only fields that are 0 get updated with the source data.
• User Alignment: The JOIN on ut.user_id = u2.id guarantees we're updating the correct user's statistics.
• Transaction Protection: For production environments, wrap the update in a transaction to safely verify changes before committing:

  BEGIN TRANSACTION;
  -- Run your update query here
  -- Re-run the validation SELECT to check changes
  COMMIT; -- Or ROLLBACK if something looks off
  

• Performance: If your tables are large, add indexes on users2.id and user_totals.user_id—this will speed up the join operation significantly.

After running the update, re-run the validation query to confirm all target fields that were 0 have been populated with the values from users2.

内容的提问来源于stack exchange，提问作者I. Dynin