Oracle：使用Table_2的最接近匹配值更新Table_1列数据

Got it, let's figure out how to update each value in Table_1 to the nearest value from Table_2. The core idea is to calculate the absolute difference between each value in Table_1 and every value in Table_2, then pick the Table_2 value with the smallest difference for each Table_1 entry.

Below are practical solutions for popular SQL databases:

MySQL 8.0+ (Using Window Functions) #

Window functions make this straightforward. We'll use ROW_NUMBER() to rank matches by their absolute difference, then pick the top-ranked (closest) value for each entry:

WITH ranked_matches AS (
    SELECT
        t1.original_value,
        t2.match_value,
        ABS(t1.original_value - t2.match_value) AS difference,
        ROW_NUMBER() OVER (
            PARTITION BY t1.original_value
            ORDER BY ABS(t1.original_value - t2.match_value), t2.match_value
        ) AS rank_num
    FROM Table_1 t1
    CROSS JOIN Table_2 t2
)
UPDATE Table_1 t1
JOIN ranked_matches rm ON t1.original_value = rm.original_value
SET t1.original_value = rm.match_value
WHERE rm.rank_num = 1;

Note: The ORDER BY ... t2.match_value handles ties (if two Table_2 values are equally close), picking the smaller one. Adjust this to DESC if you want the larger value instead.

PostgreSQL #

PostgreSQL uses similar window function logic. Here's the update query:

WITH ranked_matches AS (
    SELECT
        t1.original_value,
        t2.match_value,
        ROW_NUMBER() OVER (
            PARTITION BY t1.original_value
            ORDER BY ABS(t1.original_value - t2.match_value), t2.match_value
        ) AS rank_num
    FROM Table_1 t1
    CROSS JOIN Table_2 t2
)
UPDATE Table_1 t1
SET original_value = rm.match_value
FROM ranked_matches rm
WHERE t1.original_value = rm.original_value AND rm.rank_num = 1;

SQL Server #

For SQL Server, the syntax is slightly different but follows the same logic:

WITH ranked_matches AS (
    SELECT
        t1.original_value,
        t2.match_value,
        ROW_NUMBER() OVER (
            PARTITION BY t1.original_value
            ORDER BY ABS(t1.original_value - t2.match_value), t2.match_value
        ) AS rank_num
    FROM Table_1 t1
    CROSS JOIN Table_2 t2
)
UPDATE t1
SET t1.original_value = rm.match_value
FROM Table_1 t1
JOIN ranked_matches rm ON t1.original_value = rm.original_value
WHERE rm.rank_num = 1;

Legacy MySQL (Pre-8.0, No Window Functions) #

If you're stuck on an older MySQL version, use a correlated subquery to find the closest match:

UPDATE Table_1 t1
SET original_value = (
    SELECT t2.match_value
    FROM Table_2 t2
    ORDER BY ABS(t1.original_value - t2.match_value), t2.match_value
    LIMIT 1
);

Example Behavior #

Using your sample data:

• 1039 in Table_1 will match 1038 (difference of 1, closest)
• 3900 will match 3903 (difference of 3, closest)
• 2345 will match 2340 (difference of 5, closest)

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