MySQL新手求助：如何用INNER JOIN UPDATE填充空表？

Hey there, let's break down what's going on here and fix your issue!

First off, your core misunderstanding is this: UPDATE statements only modify existing rows—they can't insert new ones into an empty table. It's like trying to rearrange items in an empty cabinet; there's nothing there to work with, so the operation does nothing.

Let's start with your original query:

UPDATE exp_credit_tracker_designation INNER JOIN exp_credit_tracker_assoc ON exp_credit_tracker_designation.association_id = exp_credit_tracker_assoc.id SET exp_credit_tracker_assoc.id = exp_credit_tracker_designation.association_id

This has two key problems:

1. Since exp_credit_tracker_designation is empty, the INNER JOIN can't find any matching rows—so no updates happen at all.
2. You've got the assignment backwards. You should be copying values from exp_credit_tracker_assoc to your target table, not modifying the source table's data.

Correct Approach: Use INSERT...SELECT to Fill the Empty Table #

Since you need to populate an empty table, INSERT ... SELECT is the right tool for the job. It lets you pull data from one table and insert it directly into another.

Assuming you want to import rows from exp_credit_tracker_assoc where the record represents a designation (like rows where association_name is 'designation'), here's the query you need:

INSERT INTO exp_credit_tracker_designation (association_id, designation_name)
SELECT 
    a.id AS association_id,
    a.association_name AS designation_name
FROM exp_credit_tracker_assoc a
-- Filter to only the rows that represent designations (adjust this WHERE clause to match your logic)
WHERE a.association_name = 'designation';

This will pull the relevant ID and name from your source table and insert them into exp_credit_tracker_designation. If the id column in your target table is an auto-increment primary key, MySQL will generate those values automatically.

For Future Updates (When the Table Has Data) #

If later on you need to update existing rows in exp_credit_tracker_designation using data from exp_credit_tracker_assoc, here's the correct UPDATE JOIN syntax (with aliases for readability):

UPDATE exp_credit_tracker_designation d
INNER JOIN exp_credit_tracker_assoc a 
    ON d.association_id = a.id
SET 
    d.designation_name = a.association_name; -- Sync the name from the assoc table to the designation table

This works because now there are existing rows in d to match against a via the join.

To recap: Use INSERT ... SELECT for empty tables, reserve UPDATE JOIN for modifying rows that already exist.

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