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?
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With