Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Inner join within update statement in PostgreSQL

Tags:

postgresql

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

like image 725
Sarfaraz Makandar Avatar asked Jun 26 '14 10:06

Sarfaraz Makandar


People also ask

Can we use inner join in UPDATE statement?

SQL Server UPDATE JOIN syntax To query data from related tables, you often use the join clauses, either inner join or left join. In SQL Server, you can use these join clauses in the UPDATE statement to perform a cross-table update.

How do I UPDATE my inner join?

SQL UPDATE JOIN could be used to update one table using another table and join condition. UPDATE tablename INNER JOIN tablename ON tablename. columnname = tablename.

How can I UPDATE two table in one query?

You can't update two tables at once, but you can link an update into an insert using OUTPUT INTO , and you can use this output as a join for the second update: DECLARE @ids TABLE (id int); BEGIN TRANSACTION UPDATE Table1 SET Table1. LastName = 'DR.


1 Answers

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;
like image 98
a_horse_with_no_name Avatar answered Sep 28 '22 13:09

a_horse_with_no_name