I want to update column 'location_cost' under 'animals' table basis cost stated under 'location_costs' table. Primary key is location to join both tables. I have tried the following code but it gives me syntax error.

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

Error: syntax error at or near "SET" SET animals.location_cost = location_costs.costs

I am attaching a picture which gives an idea for the tables and columns here:

Tables View

I am not able to decipher the error and would appreciate if someone can help me with this code.

Thank you.

CodePudding user response：

If you want to update only the rows in animals that have a matching location in location_costs then use this syntax:

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

If you want to update all the rows of animals (even the rows without a a matching location in location_costs will be updated to null), then use a correlated subquery:

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

CodePudding user response：

Remove the * symbol after alias and it'll work. The query should be as such :

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

CodePudding user response：

If animalid is a column with unique values, try using an alias as follows:

Update animals
Set location_cost = location_costs.costs
From animals As a Left Join location_costs On (a.location = location_costs.location)
Where animals.animalid = a.animalid;