PostgreSQL UPDATE doesn't seem to update some rows

Question

I am trying to update a table from another table, but a few rows simply don't update, while the other million rows work just fine.

The statement I am using is as follows:

UPDATE lotes_infos l
SET quali_ambiental = s.quali_ambiental
FROM sirgas_lotes_centroid s
WHERE l.sql = s.sql AND l.quali_ambiental IS NULL;

It says 647 rows were updated, but I can't see the change. I've also tried without the is null clause, results are the same.

If I do a join it seems to work as expected, the join query I used is this one:

SELECT sql, l.quali_ambiental, c.quali_ambiental FROM lotes_infos l
JOIN sirgas_lotes_centroid c
USING (sql)
WHERE l.quali_ambiental IS NULL;

It returns 787 rows, (some are both null, that's ok), this is a sample from the result from the join:

    sql     | quali_ambiental | quali_ambiental
------------+-----------------+-----------------
 1880040001 |                 | PA 10
 1880040001 |                 | PA 10
 0863690003 |                 | PA 4
 0850840001 |                 | PA 4
 3090500003 |                 | PA 4
 1330090001 |                 | PA 10
 1201410001 |                 | PA 9
 0550620002 |                 | PA 6
 0430790001 |                 | PA 1
 1340180002 |                 | PA 9

I used QGIS to visualize the results, and could not find any tips to why it is happening. The sirgas_lotes_centroid comes from the other table, the geometry being the centroid for the polygon. I used the centroid to perform faster spatial joins and now need to place the information into the table with the original polygon.

The sql column is type text, quali_ambiental is varchar(6) for both.

If a directly update one row using the following query it works just fine:

UPDATE lotes_infos
SET quali_ambiental = 'PA 1'
WHERE sql LIKE '0040510001';

The SELECT can return a row in lotes_infos multiple times, but the UPDATE will update each row at most once. Maybe the matches value of s.quali_ambiental is null, so your update doesn't change anything. — jjanes, Jun 20 '20 at 13:46
*Always* include `CREATE TABLE` statements with any such question. Makes it so much easier to get a solution. — Erwin Brandstetter, Jun 20 '20 at 14:57
jjanes, you were onto it, the data "not being updated" was duplicated, so only the first matching value was passed on. Thanks for the insight. — Calil, Jul 02 '20 at 16:34

Erwin Brandstetter · Answer 1 · 2020-06-22T00:10:05.333

If you don't see results of a seemingly sound data-modifying query, the first question to ask is:

Did you commit your transaction?

Many clients work with auto-commit by default, but some do not. And even in the standard client psql you can start an explicit transaction with BEGIN (or syntax variants) to disable auto-commit. Then results are not visible to other transactions before the transaction is actually committed with COMMIT. It might hang indefinitely (which creates additional problems), or be rolled back by some later interaction.

That said, you mention: some are both null, that's ok. You'll want to avoid costly empty updates with something like:

UPDATE lotes_infos l
SET    quali_ambiental = s.quali_ambiental
FROM   sirgas_lotes_centroid s
WHERE  l.sql = s.sql
AND    l.quali_ambiental IS NULL
AND    s.quali_ambiental IS NOT NULL;  --!

How do I (or can I) SELECT DISTINCT on multiple columns?

The duplicate 1880040001 in your sample can have two explanations. Either lotes_infos.sql is not UNIQUE (even after filtering with l.quali_ambiental IS NULL). Or sirgas_lotes_centroid.sql is not UNIQUE. Or both.

If it's just lotes_infos.sql, your query should still work. But duplicates in sirgas_lotes_centroid.sql make the query non-deterministic (as @jjanes also pointed out). A target row in lotes_infos can have multiple candidates in sirgas_lotes_centroid. The outcome is arbitrary for lack of definition. If one of them has quali_ambiental IS NULL, it can explain what you observed.

My suggested query fixes the observed problem superficially, in that it excludes NULL values in the source table. But if there can be more than one non-null, distinct quali_ambiental for the same sirgas_lotes_centroid.sql, your query remains broken, as the result is arbitrary.You'll have to define which source row to pick and translate that into SQL.

Here is one example how to do that (chapter "Multiple matches..."):

Updating the value of a column

Always include exact table definitions (CREATE TABLE statements) with any such question. This would save a lot of time wasted for speculation.

Aside: Why are the sql columns type text? Values like 1880040001 strike me as integer or bigint. If so, text is a costly design error.

thanks Erwin, the changes are committed, on the first update all other rows were correctly updated, except these few ones. Also I should mention that updating a single row works, I will update the question showing this. On the side note `sql` is the merge of three values `S`, `Q` and `L`, they could be like S: 001, Q: 003 and L 0005, if integer the merge would result as 135, also 'L' could be like 'CD01' — Calil, Jun 20 '20 at 10:53
@HelderCalil: jjanes raised an interesting point. I elaborated on that in an update. Also, it's typically best to keep `S`, `Q`, and `L` as separate columns if those are separate values. Even more so, if two of them can be `integer` and the third must be `text`. — Erwin Brandstetter, Jun 20 '20 at 14:53

PostgreSQL UPDATE doesn't seem to update some rows

1 Answers1