求助：使用LEFT JOIN跨表更新SQL中animals表location_cost列的语法错误问题

Your original query has two main issues:

1. You don't need .* after animals in the UPDATE clause—you only specify the table name you're updating.
2. The placement of the JOIN and SET clauses varies by SQL database system, and your current order doesn't match standard syntax for most systems.

Here are corrected versions for the most common databases:

For MySQL/MariaDB #

In MySQL, you define the join first, then the SET clause:

UPDATE animals
LEFT JOIN location_costs ON animals.location = location_costs.location
SET animals.location_cost = location_costs.costs;

If you want to keep the existing location_cost value when there's no matching entry in location_costs (instead of setting it to NULL), use COALESCE:

UPDATE animals
LEFT JOIN location_costs ON animals.location = location_costs.location
SET animals.location_cost = COALESCE(location_costs.costs, animals.location_cost);

For PostgreSQL #

PostgreSQL uses a FROM clause to include the joined table, and links it to the target table in the WHERE clause:

UPDATE animals
SET location_cost = location_costs.costs
FROM location_costs
WHERE animals.location = location_costs.location;

To handle cases where there's no matching location (and preserve existing values), use a LEFT JOIN in the FROM:

UPDATE animals
SET location_cost = COALESCE(lc.costs, animals.location_cost)
FROM animals a
LEFT JOIN location_costs lc ON a.location = lc.location
WHERE animals.location = a.location;

For SQL Server #

SQL Server uses table aliases and places the join in a FROM clause before the SET:

UPDATE a
SET location_cost = lc.costs
FROM animals a
LEFT JOIN location_costs lc ON a.location = lc.location;

Again, to preserve existing values when no match exists:

UPDATE a
SET location_cost = ISNULL(lc.costs, a.location_cost)
FROM animals a
LEFT JOIN location_costs lc ON a.location = lc.location;

The key takeaway is matching the syntax to your specific database system—this should resolve the "syntax error at or near 'SET'" message you're seeing.

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