Different number of rows affected when using select or update with the same parameters - PostgreSQL

Question

I have two SQL statements with the same Join and Where clause, but I have a problem that the select statement gives me a different number of rows (in my case 42), because the update statement will change (in my case 80, that all the rows of the table).

Here is the first one (I use this to check how many rows will be affected):

SELECT COUNT(*) FROM classes AS c INNER JOIN programs AS p ON c.Pr_ID = p.Pr_ID AND p.Ma_ID = 8; --> returns: 32 

and here is the second one (this does the job, it will update one field of the table classes):

 UPDATE classes SET Cl_Status = 3 FROM classes AS c INNER JOIN programs AS p ON c.Pr_ID = p.Pr_ID AND p.Ma_ID = 8; --> returns: 80 (!) 

The difference between the first and second operator is only the first line, everything else is the same.

Does anyone know what has changed to get the same number of rows in both statements?

Answer 1

Do you want to

 UPDATE classes SET CL_Status = 3 FROM programs WHERE classes.Pr_ID = programs.Pr_ID AND programs.Ma_ID = 8 

Answer 2

The UPDATE query is different from the SELECT query, it has a separate connection and will touch all records because of this.

From the manual:

Note that the target table should not appear in the from_list, unless you intend to merge