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

how to not update all rows and only restrict to the where clause – Postgresql

I have an update query in postgres as such:

update table1 e
set (col1,col2) = 
(select col1,col2 from table2 se
where  e.id = se.id and se.col2 is not null);

However this updates all rows even the ones that are nulls for se.col2. The rows are updated as nulls. I want to just update where se.col2 is not null . Not all rows.

Tags:

2

Answers


  1. 0

    This is simple if you use the (non-standard) FROM clause of UPDATE:

    UPDATE table1 e
SET (col1, col2) = (se.col1, se.col2)
FROM table2 se
WHERE e.id = se.id AND se.col2 IS NOT NULL;

    This assumes that table2.id is unique, or at least that no more than one row of table2 matches any given table1 row.

    Login or Signup to reply.
  2. 0 
    update table1 e
set (col1,col2) = (
  select col1,col2 from table2 se
  where  e.id = se.id
)
where exists (
  select id from table2 se2 
  where se2.id = e.id 
  and col2 is not null
);

    DB Fiddle to play with.

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

Back To Top
Search