Postgres SQL SELECT and UPDATE behaving differently

Question

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

@EMett COUNT(*) includes everything, NULL values included and also if a row contains NULL values entirely ( example SQLFiddle ) — Radu Gheorghiu
– Radu Gheorghiu, Commented Aug 12, 2015 at 11:43
Try update FEA instead of update fromemailaddress - how many rows are being updated ? :) — Horia
– Horia, Commented Aug 12, 2015 at 11:46
Why are you using a LEFT JOIN if you want to apply a condition to the second table? Wouldn't it be more straightforward to directly use INNER JOIN? Besides that, I never used a JOIN in an UPDATE sentence (but I'm more used to Oracle, and dealing with these cases with EXISTS / IN clauses) — Leo Lozes
– Leo Lozes, Commented Aug 12, 2015 at 11:47

Gordon Linoff · Accepted Answer · 2015-08-12 12:29:41Z

3

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.

edited Aug 12, 2015 at 12:29

answered Aug 12, 2015 at 11:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CalebB Over a year ago

Also worth noting the functionality of that operator being a case sensitive regex match.

Kamran Khan · Accepted Answer · 2015-08-12 11:55:43Z

1

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';

answered Aug 12, 2015 at 11:55

Kamran Khan

1,11111 silver badges24 bronze badges

Collectives™ on Stack Overflow

Postgres SQL SELECT and UPDATE behaving differently

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related