Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

Here is the first one (I use this to check how many lines 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 the second (this makes the work, 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 the second statement is only the first row, everything else is the same.

Does anyone know what do change to get the same number of rows at both statements?

like image 573
Magistratsbeamter Avatar asked Feb 23 '23 01:02

Magistratsbeamter


1 Answers

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

From the manual:

Note that the target table must not appear in the from_list, unless you intend a self-join

like image 94
Frank Heikens Avatar answered Apr 08 '23 08:04

Frank Heikens