Update One Table with Data From Another Table

Note: If this doesn’t meet what you’re looking for in terms of updating data from another table, leave a comment below and I’ll look into it. Alternatively, you can go back to the index page of SQL queries.

There are at least 3 variations for updating data between tables.

Update From Another Table Directly

Situation: You want to update the fields for a particular row in the child table with data from the parent table.

MySQL

UPDATE child_table c
INNER JOIN parent_table p  
ON      
   p.id = c.parent_id  
SET     
   c.name = concat(p.name, ' > Child'  
WHERE c.parent_id = 123;

Postgres

UPDATE child_table AS c 
SET   
 name = concat(p.name, ' > Child') 
FROM parent_table AS p 
WHERE c.parent_id= p.id and c.parent_id = 123;

Update From Another Table That Has Conditions

Situation: You want to edit the fields for a particular row in the child table with data from the parent table. Similar to the earlier example above. The difference is, not all rows from the parent table qualify. The change here is to turn the parent table with the conditions into an alias.

MySQL

UPDATE child_table c 
INNER JOIN (
  SELECT [fields you need] FROM parent_table WHERE [your conditions here]
) p
ON      
   p.id = c.parent_id  
SET     
   c.name = concat(p.name, ' > Child'  
WHERE c.parent_id = 123;

Postgres

UPDATE child_table AS c 
SET   
 name = concat(p.name, ' > Child') 
FROM (
  SELECT [fields you need] 
  FROM parent_table WHERE [your conditions here]
) AS p 
WHERE c.parent_id= p.id 
AND c.parent_id = 123;

Update with Select SUM From Another Table

Situation: You have a table that is the parent of another child table. You want to sum a field in the child table and change the value to the corresponding parent record in the parent table.

MySQL

UPDATE parent_table pt  
INNER JOIN (
  SELECT pt_id, COALESCE(SUM(field1),0) as field1_sum    
  FROM child_table     
  GROUP BY pt_id ) i 
ON 
  pt.id = i.pt_id 

SET pt.total_field1 = i.field1_sum [WHERE predicate];

Postgres

UPDATE parent_table pt 
SET pt.total_field1 = i.field1_sum 
FROM  (  
  SELECT pt_id, COALESCE(SUM(field1),0) as field1_sum   
  FROM child_table  
  GROUP BY pt_id ) i 
WHERE i.pt_id = pt.id [and more WHERE predicate];