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.
- From another table that has no conditions
- From another table that has conditions
- Using Select Sum from another table
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 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];
I need to keep notes on postgres queries mostly for data-engineering , database admin purposes. For e.g. mass inserting and updating records to prepare a LIVE web app that I build for a client.
If you have frequently used queries that you find useful, do share with me here as well.