Postgres SQL SELECT and UPDATE behaving differentl

问题:

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 count(*)  from fromemailaddress LEFT JOIN email ON 
(email.fromemailaddress = fromemailaddress.fromemailaddress) 
WHERE LOWER(email.subject) ~ 'tester';
 count
-------
  7065

But:

superfrr=# update fromemailaddress set call=true  from fromemailaddress 
 as fea LEFT JOIN email ON (email.fromemailaddress = fea.fromemailaddress)
 WHERE LOWER(email.subject) ~ 'tester';
UPDATE 13935

回答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
    set call = true 
    from email
    where email.fromemailaddress = fromemailaddress.fromemailaddress and
          LOWER(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.

回答2:

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

select count(PrimaryKey Field)  from fromemailaddress LEFT JOIN email ON 

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