如何从MySQL表A列取数更新同外键等行数的另一表B列？

Hey there! Since your two tables share the same foreign key and have an equal number of rows (meaning a one-to-one match between each entry), using an UPDATE with JOIN is the ideal solution here—it ensures every row in the target table gets the exact corresponding value from the source table.

Step 1: Basic Update Statement #

Let’s define some placeholder names to make this concrete:

• Source table (holds the data you want to copy): source_table, column A is column_a
• Target table (needs its column B updated): target_table, column B is column_b
• Shared foreign key column that links the two tables: shared_fk_id

Here’s the core SQL command to run:

UPDATE target_table t
JOIN source_table s ON t.shared_fk_id = s.shared_fk_id
SET t.column_b = s.column_a;

Step 2: Verify Before Updating (Don’t Skip This!) #

Before executing the update, always double-check that the matches are correct to avoid accidental data overwrites. Run this SELECT query to preview exactly what changes will happen:

SELECT 
  t.shared_fk_id,
  t.column_b AS original_b_value,
  s.column_a AS new_b_value
FROM target_table t
JOIN source_table s ON t.shared_fk_id = s.shared_fk_id;

This will show you a side-by-side comparison of the original values in column B and the new values coming from column A—make sure every row lines up as you expect.

A Quick Note #

Since you confirmed the tables have the same number of rows and matching foreign keys, this query will update every row in target_table.column_b without missing or duplicating any values. If there were mismatched keys or extra rows, you’d need to adjust the logic, but your use case fits perfectly with this straightforward approach.

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