Updating a table by referencing another table

Question

I have a table CustPurchase (name, purchase) and another table CustID (id, name).

I altered the CustPurchase table to have an id field. Now, I want to populate this newly created field by referencing the customer ids from the CustID table, using:

UPDATE CustPurchase
   SET CustPurchase.id = CustID.id 
 WHERE CustPurchase.name = CustID.name;

I keep getting syntax errors!

Answer 1

I believe you are after the useful UPDATE FROM syntax.

UPDATE CustPurchase SET id = CI.id 
FROM
   CustPurchase CP
   inner join CustID CI on (CI.name = CP.name)

This might have to be the following:

UPDATE CustPurchase SET id = CI.id 
FROM
   CustID CI 
WHERE
   CI.name = CustPurchase.name

Sorry, I'm away from my Postgres machine; however, based upon the reference, it looks like this is allowable. The trouble is whether or not to include the source table in the from_list.

Answer 2

Joining by name is not an ideal choice, but this should work:

UPDATE custpurchase
   SET id = (SELECT c.id
               FROM CUSTID c
              WHERE c.name = custpurchase.name)

The caveat is that if there's no match, the value attempting to be inserted would be NULL. Assuming the id column won't allow NULL but will allow duplicate values:

UPDATE custpurchase
   SET id = (SELECT COALESCE(c.id, -99)
               FROM CUSTID c
              WHERE c.name = custpurchase.name)

COALESCE will return the first non-NULL value. Making this a value outside of what you'd normally expect will make it easier to isolate such records & deal with appropriately.

Otherwise, you'll have to do the updating "by hand", on a name by name basis, to correct instances that SQL could not.