Postgres SQL SELECT和UPDATE行为不同 [英] Postgres SQL SELECT and UPDATE behaving differently

问题描述

我不知道为什么下面的SELECT为什么找到7065条记录，但是更新说它更新了13935条记录，这是表中的每条记录.

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.

有人可以建议原因吗?

superfrr=# select count(*)  from fromemailaddress LEFT JOIN email ON 
(email.fromemailaddress = fromemailaddress.fromemailaddress) 
WHERE LOWER(email.subject) ~ 'tester';
 count
-------
  7065

但是:

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

推荐答案

使用~表示您正在使用Postgres.如果是这样，则这两个查询在做非常不同的事情.在Postgres中，您没有在from子句中包含要更新的表.

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.

所以，我想你想要

update fromemailaddress
    set call = true 
    from email
    where email.fromemailaddress = fromemailaddress.fromemailaddress and
          LOWER(email.subject) ~ 'tester';

您的版本正在更新fromemailaddress中的所有行，因为没有条件连接update子句中的fromemailaddress和from子句中的fea.

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.

另外请注意:left join是不必要的，因为where子句仍将其转换为内部联接.

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

这篇关于Postgres SQL SELECT和UPDATE行为不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！