PSQL not updating uniquely but copying value onto all entries---Foreign Key update

Question

This is looking for solutions in PSQL or PGAdmin III:

I have set up the SQL command as follows as per this question:

UPDATE members
SET pers_id=P.pers_id
--SELECT *
FROM persons P
INNER JOIN members M
ON M.full_name=P.full_name

However it is not updating the column pers_id with the individual value instead it is copying the data point from the first query onto all entries.

It should be noted that pers_id is a foreign key in the members table.

What does `SELECT P.pers_id, P.full_name FROM persons P INNER JOIN members M ON M.full_name = P.full_name` show you? — Tim Biegeleisen, Feb 17 '16 at 04:53
[From the manual](http://www.postgresql.org/docs/current/static/sql-update.html): "*Note that the target table must **not** appear in the from_list, unless you intend a self-join*". The linked question is for SQL Server which uses a different syntax. — , Feb 17 '16 at 08:04
The above query updated with all the same number. 754, the id from the first entry as if it started with `SELECT * FROM persons p` — AER, Feb 18 '16 at 04:32

score 1 · Accepted Answer · edited Feb 17 '16 at 08:05

1

Try this:

UPDATE members
SET pers_id=persons.pers_id
FROM persons
WHERE members.full_name = persons.full_name

edited Feb 17 '16 at 08:05

answered Feb 17 '16 at 05:22

Khalid Amin

Thanks. For some reason I thought that the table.column syntax didn't work in psql – AER Feb 18 '16 at 04:32

1 Answers1