Postgres Sql Select And Update Behaving Differently

I can't see why the SELECT below finds 7065 records, but the update says it updates 13935 records, which is every record in the table. Can anyone suggest why? superfrr=# select cou

Solution 1:

The use of ~ suggests that you are using Postgres. If so, the two queries are doing very different things. In Postgres, you don't include the table being updated in the from clause.

So, I think you want:

update fromemailaddress
    setcall=truefrom email
    where email.fromemailaddress = fromemailaddress.fromemailaddress andLOWER(email.subject) ~'tester';

Your version is updating all rows in fromemailaddress because there is no condition connecting fromemailaddress in the update clause and fea in the from clause.

Also note: the left join is unnecessary because the where clause turns it into an inner join anyway.

Solution 2:

You must count on primary key it will returns all values because count do not work on null values. Hopes this helps thanks

selectcount(PrimaryKey Field)  from fromemailaddress LEFTJOIN email ON

(email.fromemailaddress = fromemailaddress.fromemailaddress) WHERE LOWER(email.subject) ~ 'tester';