The following query returns 2036 rows:
SELECT "FooUID" from "Foo" f LEFT JOIN "Bar" b ON f."BarUID" = b."BarUID" WHERE f."BarUID" IS NOT NULL AND b."BarUID" IS NULL
But the following statement only updated 1870 lines:
UPDATE "Foo" f1 set "BarUID" = 'aNewUID' WHERE f1."FooUID" IN ( SELECT f2."FooUID" from "Foo" f2 LEFT JOIN "Bar" b ON f2."BarUID" = b."BarUID" WHERE f2."BarUID" IS NOT NULL AND b."BarUID" IS NULL )
How is this possible?
EDIT 1: The first query continues to return 166 rows, and the second continues to update 0 rows.
EDIT 2:
The next subquery returns a string containing the UID, but the outer query returns 0 rows.
SELECT * from "Foo" f1 WHERE f1."FooUID" = ( SELECT f2."FooUID" FROM "Foo" f2 LEFT JOIN "Bar" b ON f2."BarUID" = b."BarUID" WHERE f2."BarUID" IS NOT NULL AND b."BarUID" IS NULL LIMIT 1 )
I've gone mad?
EDIT 3:
The following statement provided by @wildplasser was able to update the remaining 166 lines:
UPDATE "Foo" ff SET "BarUID" = 'aNewUID' WHERE ff."BarUID" IS NOT NULL AND NOT EXISTS ( SELECT * FROM "Bar" bb WHERE bb."BarUID"= ff."BarUID" )
However, I still do not understand why the original did not pick them up. If the "FooUID" 166 "FooUID" s, why don't they match the rows in the "Foo" table with IN ?
EDIT 4: The more I think about it, this background may be important:
All this happened on a database server that was recently cloned from another. I talked to the IT guy who did the cloning, and it turned out that he did not close the application running on top of the original database before dumping it to clone it. This means that the database basically probably knocked down the average transaction (I don't know how shamelessly). Is it possible that something in the database remained in a damaged state, which will lead me to these phantom lines?
Unfortunately, I can no longer reproduce it, because the wildplasser fix works. The original database (up and serving the application again) does not have any of the invalid data that I tried to fix on the copy, and especially from the traces of weather forecasters that I saw.
I should mention that before starting the fix, I reduced the problem to the most elementary absurdity: I first selected FooUID from the FooUID in Edit 2, copied it to the clipboard, and then executed the query, choosing from Foo where FooUID equal to the pasted value - this is still returned 0 rows.