UPDATE with a join not working

Question

This update query is not working it just hangs

UPDATE tempimportmailer ti
SET lead_id = leads.id
FROM leads 
INNER JOIN tempimportmailer
ON leads.full_name = tempimportmailer.name AND
leads.address = tempimportmailer.add1 AND
leads.zip_code = tempimportmailer.zip
WHERE tempimportmailer.lead_id = 0

I have also tried the following, but the results were not what I had anticipated, the lead_numbers did not match up correctly when I viewed the tempimportmailer table.

UPDATE tempimportmailer
SET lead_id = leads.id
FROM leads 
WHERE leads.full_name = tempimportmailer.name AND leads.address = tempimportmailer.add1 AND
leads.zip_code = tempimportmailer.zip 
AND tempimportmailer.lead_id = 0;

I am new to PostgreSQL so please go easy on me.

Hard to help without knowing anything about your data, or what "did not match up" exactly means. — JimmyB, Sep 15 '15 at 13:47
What does `select leads.id FROM tempimportmailer, leads WHERE leads.full_name = tempimportmailer.name AND leads.address = tempimportmailer.add1 AND leads.zip_code = tempimportmailer.zip AND tempimportmailer.lead_id = 0 group by leads.id having count(*) > 1;` return? — JimmyB, Sep 15 '15 at 13:49
Your 2nd query looks ok. Please be more specific about "did not match up correctly" - exactly what did not work "properly"? — Bohemian, Sep 15 '15 at 13:49
The lead_id in tempimportmailer was not correct when I checked the leads table. — ja11946, Sep 15 '15 at 13:52
I was just reading something that if there are multiple joins on an UPDATE if one or more match it will update with those values. I might need to use a sub select?? I need all three of the joins to be true for the update to occur — ja11946, Sep 15 '15 at 13:53
You should ***not*** repeat the table to be updated in the `FROM` or `JOIN` clause. The second statement is definitely "more" correct than the first one. But to know what's wrong you should post some sample data and the expected output. **Edit** your question, don't post additional information in comments — , Sep 15 '15 at 13:56

score 0 · Answer 1 · answered Sep 15 '15 at 14:27

Okay guys here is what worked... I read on the postgresql doc site the following "When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_list, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

Because of this indeterminacy, referencing other tables only within sub-selects is safer, though often harder to read and slower than using a join."

I rewrote the code and this seemed to do the trick:

UPDATE tempimportmailer
SET lead_id = subquery.id
FROM (SELECT id, full_name, address, zip_code FROM leads) AS subquery
WHERE tempimportmailer.lead_id = 0 AND tempimportmailer.name = subquery.full_name AND tempimportmailer.add1 = subquery.address
AND tempimportmailer.zip = subquery.zip_code

Thanks for all your responses. Hope this help someone else.

score 0 · Answer 2 · edited May 23 '17 at 12:29

Basically your second query in the question should just work (better than your current self-answer).
Simplify with table aliases and the subquery in your answer does nothing useful, remove it:

UPDATE tempimportmailer t
SET    lead_id = l.id
FROM   leads l
WHERE  t.lead_id = 0
AND    t.name = l.full_name
AND    t.add1 = l.address
AND    t.zip  = l.zip_code
AND    t.lead_id IS DISTINCT FROM l.id;  -- may be useful

The last predicate is useful when some of the target rows already have the value they are assigned. Details:

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

UPDATE with a join not working

2 Answers2