CODEWITHSUNDEEP
mysqlsqlpostgresql
António Cabral
António Cabral

Reputation: 169

UPDATE with nested query in PostgreSQL with unwanted results

Due to its geographic capabilities I'm migrating my database from MySQL to PostgreSQL/PostGIS, and SQL that used to be so trivial is now are becoming painfully slow to overcome.

In this case I use a nested query to obtain the results in two columns, having in 1st column an ID and in the 2nd a counting result and insert those results in table1. EDIT: This is the original MySQL working code that I need to be working in PostgreSQL:

UPDATE table1 INNER JOIN (
   SELECT id COUNT(*) AS cnt
   FROM table2
   GROUP BY id
) AS c ON c.id = table1.id
SET table1.cnt = c.cnt

The result is having all rows with the same counting result, that being the 1st counting result of the nested select. In MySQL this would be solved easily. How would this work in PostgreSQL? Thank you!

Upvotes: 0

Views: 2187

Answers (1)

wildplasser
wildplasser

Reputation: 44250

UPDATE table1 dst
SET cnt = src.cnt
FROM (SELECT id, COUNT (*) AS cnt
     FROM table2 
     GROUP BY id) as src
WHERE src.id = dst.id
;

Upvotes: 0

Related Questions