Inner join within update statement in PostgreSQL

Question

I have table called temp_table which consist of following rows:

 cola colb result
 ----------------
  p4   s1    0
  p8   s1    0
  p9   s1    0
  p5   f1    0
  p8   f1    0

Now I need to update result column with the count(*) of colb. For which i am trying the following query:

update tem_table
set result = x.result
from tem_table tt
inner join(select colb,count(*) as result from tem_table group by colb) x
on x.colb = tt.colb;

And selecting distinct colb and result from temp_table:

select distinct colb,result from tem_table;

Getting output:

colb result
-----------
 s1    3
 f1    3

But the expected output is:

colb result
-----------
 s1    3
 f1    2

I am not getting where I am getting wrong in my query? Please help me.Thanks

user330315 · Accepted Answer · 2014-06-26 10:37:06Z

26

You should not repeat the table to be updated in the from clause. This will create a cartesian self join.

Quote from the manual:

Note that the target table must not appear in the from_list, unless you intend a self-join (in which case it must appear with an alias in the from_list)

^{(Emphasis mine)}

Unfortunately UPDATE does not support explicit joins using the JOIN keyword. Something like this should work:

update tem_table
  set result = x.result
from (
    select colb,count(*) as result 
    from tem_table 
    group by colb
) x
where x.colb = tem_table.colb;

answered Jun 26, 2014 at 10:37

user330315

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

Collectives™ on Stack Overflow

Inner join within update statement in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related