MySQL跨表更新数据：基于现有表更新目标表指定字段

Hey there, let's work through how to solve this sync problem between Table 1 and Table 2. From what you described, you want Table 2 to keep all its original records where prefix=0, while fully syncing the prefix=1 records from Table 1—meaning any existing prefix=1 entries in Table 2 should be updated to match Table 1, and any extra prefix=1 entries in Table 2 (that aren't present in Table 1's prefix=1 set) should be removed. Here's a step-by-step solution:

Our core goal is twofold:

• Preserve all prefix=0 records in Table 2 (no changes to these)
• Sync prefix=1 records to match Table 1 exactly (update existing, add new, remove missing)

SQL Implementation (MySQL Example) #

Assuming both tables share the same structure (id, value, prefix columns) and id is a primary/unique key, here are two reliable approaches:

Approach 1: Atomic Update & Cleanup (Recommended)

This method ensures we only modify the prefix=1 records, keeping your prefix=0 data intact:

-- Step 1: Remove any `prefix=1` records in Table 2 that don't exist in Table 1's `prefix=1` set
DELETE t2
FROM Table2 t2
LEFT JOIN Table1 t1 ON t2.id = t1.id AND t1.prefix = 1
WHERE t2.prefix = 1 AND t1.id IS NULL;

-- Step 2: Sync Table 1's `prefix=1` records to Table 2 (update existing, insert new)
INSERT INTO Table2 (id, value, prefix)
SELECT id, value, prefix FROM Table1 WHERE prefix = 1
ON DUPLICATE KEY UPDATE
value = VALUES(value),
prefix = VALUES(prefix);

Approach 2: Simplified Reset & Insert

If you don't need to preserve any existing prefix=1 records in Table 2 (just want a full overwrite with Table 1's prefix=1 data), use this simpler version:

-- Clear all `prefix=1` records from Table 2
DELETE FROM Table2 WHERE prefix = 1;

-- Insert all `prefix=1` records from Table 1
INSERT INTO Table2 (id, value, prefix)
SELECT id, value, prefix FROM Table1 WHERE prefix = 1;

For Other Databases (e.g., PostgreSQL) #

The logic stays the same, but the syntax for conflict handling changes:

-- Sync Table 1's `prefix=1` records (update existing, insert new)
INSERT INTO Table2 (id, value, prefix)
SELECT id, value, prefix FROM Table1 WHERE prefix = 1
ON CONFLICT (id) DO UPDATE SET
value = EXCLUDED.value,
prefix = EXCLUDED.prefix;

-- Remove extra `prefix=1` records from Table 2
DELETE FROM Table2 t2
WHERE t2.prefix = 1
AND NOT EXISTS (
    SELECT 1 FROM Table1 t1 WHERE t1.id = t2.id AND t1.prefix = 1
);

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