Copying a column from one table to another

Question

I have photos table onto which I'm trying to insert a new column from another table by filtering data with a WHERE clause. Here is the query I'm trying to execute:

ALTER TABLE photos ADD COLUMN new_count BIGINT;

INSERT INTO photos (new_count)
SELECT c.cnt
FROM c
WHERE  
    c.created = photos.created;

But in the result I am getting an error:

Error : ERROR:  invalid reference to FROM-clause entry for table "photos"
LINE 5:  c.created = photos.created

How can avoid the error and copy the column?
I'm using PostgreSQL 9.4.

How granular is your created time? Is it possible for multiple photos to have been created at the same millisecond? You want to be careful that you only update the right rows. You don't have an id column to join on? — Brian White, Jan 26 '15 at 14:12

score 2 · Accepted Answer · edited May 23 '17 at 11:57

Join in the referenced table c with a FROM clause.

UPDATE photos p
SET    new_count = c.cnt
FROM   c
WHERE  c.created = p.created
AND    c.cnt IS NOT NULL;  -- optional, depends on additional circumstances

Details in the manual.

Correlated subqueries (like demonstrated by @Andomar) are generally slower, and they also have a side effect that's typically undesirable:
If no matching row is found in c, the UPDATE proceeds regardless and new_count is set to NULL. This happens to be not wrong in this particular case (since the column is NULL by definition of the case). It's still pointless extra cost. Often, the effect is actively harmful, though.

The added condition AND c.cnt IS NOT NULL further avoids empty updates if the new value is no different from the old. Useless if c.cnt cannot be NULL. It's the simplified form of the generic check: AND c.cnt IS DISTINCT FROM p.new_count. Details (last paragraph):

How do I (or can I) SELECT DISTINCT on multiple columns?

score 1 · Answer 2 · answered Jan 26 '15 at 12:20

Use insert to add rows to a table, and update to change existing rows. Here's an example of how to update a column in photos based on a link to the c table:

update photos p
set    new_count = 
       (
       select  cnt
       from    c
       where   c.created = p.created
       )

Copying a column from one table to another

2 Answers2