0

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.

moth
  • 1,833
  • 12
  • 29

2 Answers2

3

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • yeah i understand , otherwise this will fail silently ... it will update unwanted rows... – moth Apr 04 '23 at 06:35
  • Are you [sure](https://stackoverflow.com/a/75592208/4808122) to get an *error*, test in version 14 shows undeterministic update with the *first* value... – Marmite Bomber Apr 04 '23 at 07:34
  • 1
    Well this would mean that the behaviour is even more *undeterministic* than I thought. But I would argue that it is in the responsibility of the query designer to join on a unique key in this case. – Marmite Bomber Apr 04 '23 at 13:02
  • yes i was surprised that it gives an error since the documentation on update gives an example which specifically talks about giving unwanted results https://www.postgresql.org/docs/current/sql-update.html – moth Apr 04 '23 at 15:04
  • 1
    @MarmiteBomber Hm, I must have misremembered. – Laurenz Albe Apr 05 '23 at 00:55
1
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.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348