PostgreSQL: guide with UPDATE statement not modifying rows as expected when using a subquery in WHERE clause
I'm maintaining legacy code that I'm working with an scenario with an `UPDATE` statement in PostgreSQL 13 where I'm trying to update records based on a condition derived from a subquery. The subquery retrieves IDs from another table, but it seems that my `UPDATE` isn't affecting any rows, even though the subquery returns valid IDs. Here’s the code snippet that I’m using: ```sql UPDATE orders SET status = 'shipped' WHERE customer_id IN ( SELECT id FROM customers WHERE active = true ); ``` I expected this query to update the `status` of all orders belonging to active customers. However, it returns `UPDATE 0` which indicates that no rows were updated. To debug, I ran the subquery separately and confirmed it returns the expected list of active customer IDs. I also verified that there are indeed orders associated with these customers. Here’s the subquery result for clarity: ```sql SELECT id FROM customers WHERE active = true; ``` This returns, for example, customer IDs `1, 2, 3`, but when I run the `UPDATE`, it shows no rows are affected. I also checked for any potential data mismatches between `customer_id` in the `orders` table and `id` in the `customers` table. Both columns are of type `INTEGER` and there should be no issues there. I’ve also tried changing the condition to use a `JOIN` instead of a subquery: ```sql UPDATE orders SET status = 'shipped' FROM customers WHERE orders.customer_id = customers.id AND customers.active = true; ``` Still, this results in `UPDATE 0`. Is there something I might be overlooking or a best practice I should be aware of regarding `UPDATE` queries with subqueries? Any help would be appreciated!