求助：将同公司重复location_id改为最小值，SQL Server代码适配MariaDB报错

Hey there! Let's sort out this MariaDB update issue you're hitting. Your goal is clear: for every company in your table, set all its location_id values to the smallest location_id associated with that company. Since your modified SQL Server code isn't playing nice with MariaDB, the problem almost certainly comes from syntax differences between the two databases.

Here are two reliable, MariaDB-compatible solutions to get this done:

1. Direct Join with Aggregate Subquery #

This is the most straightforward approach, leveraging a subquery to calculate each company's minimum location_id, then joining it back to your original table for the update. MariaDB handles this join-based update natively:

UPDATE your_table t1
JOIN (
    -- Get the smallest location_id for each company
    SELECT company_id, MIN(location_id) AS min_loc_id
    FROM your_table
    GROUP BY company_id
) t2 ON t1.company_id = t2.company_id
SET t1.location_id = t2.min_loc_id;

Just replace your_table with your actual table name, and company_id with the column that uniquely identifies each company.

2. Temporary Table for Larger Datasets #

If you're working with a big table, using a temporary table can improve performance by avoiding repeated calculations of the minimum location_id:

-- First, store each company's minimum location_id in a temp table
CREATE TEMPORARY TABLE company_min_loc AS
SELECT company_id, MIN(location_id) AS min_loc_id
FROM your_table
GROUP BY company_id;

-- Update the original table using the temp table
UPDATE your_table t
JOIN company_min_loc c ON t.company_id = c.company_id
SET t.location_id = c.min_loc_id;

-- Temp tables auto-delete when your session ends, but you can manually drop it if needed
DROP TEMPORARY TABLE IF EXISTS company_min_loc;

Why Your SQL Server Code Failed in MariaDB #

SQL Server allows some syntax that MariaDB doesn't support (or handles differently):

• CTE-based Updates: SQL Server lets you update directly from a CTE, but MariaDB has stricter rules around referencing CTEs in UPDATE statements (especially in older versions).
• Window Functions in UPDATE: While newer MariaDB versions support window functions, using them directly in an UPDATE clause can cause errors, whereas SQL Server is more lenient.
• SQL Server-specific Keywords: If your code used things like TOP or OUTER APPLY, those won't work in MariaDB.

Either of the solutions above should resolve the error and get your location IDs standardized correctly.

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