PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – why my sql query is not correctly updating specific records in postgres db

I have 2 postgres tables like below

table 1
A,table2_id,C
1,2,3
4,5,6
7,5,8

table 2
ID, name
2,'cat'
5,'dog'
9,'fish'

I need to replace id of dog with the id of fish inside table1 column table2_id.
At the end, table 1 should be updated like below where 2nd and 3rd rows of column table2_id should be updated with the IDs of fish from table2

table 1
A,table2_id,C
1,2,3
4,9,6
7,9,8

I wrote a query but it’s not updating any records in table1. What’s wrong with my query?

UPDATE table1
SET table2_id = table2_updated.id
FROM table2 AS table2_updated
WHERE table1.table2_id = table2_updated.id AND table2_updated.name = 'fish';
Tags:

2

Answers


  1. 0

    Not sure what you were doing in the query but this should do it for you.

    You do an INNER JOIN and as I put below you can select from the query before doing the update this way to see what would be updated (just for validation, that line is commented out), and then run the update like this.

    UPDATE table1
SET table1.table2_id = table2_updated.id
-- Select * --  can do select here instead of update to view the results
FROM table1 T1
INNER JOIN table2 AS table2_updated ON t1.table2_id = table2_updated.id 
    AND table2_updated.name = 'fish';
    Login or Signup to reply.
  2. 0

    See example. Direct update

    UPDATE table1
SET table2_id = 9
where table2_id=5;

    Update thru name

    UPDATE table1
SET table2_id = IdNew
FROM (select table2OldName.Id as IdOld, table2NewName.Id as IdNew
     from table2 AS table2OldName 
     INNER JOIN table2 AS table2NewName 
   ON table2OldName.Name='dog' and table2NewName.name = 'fish'
  ) tu
where table2_id = tu.IdOld

    Condition table1.table2_id = table2_updated.id is wrong or insufficient.
    You can take as source
    name=’dog’->id=5 — > name=’fish’->Id=9

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search