Updating all rows from a column of the same table-CodePudding

First of all, sorry for asking this question as there's a lot of similar question regarding this. But I cannot update all rows on my part based on those questions and answers.

So I have a follow-up question regarding this thread

The newly selected data - same_location_count, same_location_store, I want those data to be updated on the same table. Like, instead of just selecting, I want it to be updated as well.

From this:

store_name               | latitude    | longitude | store_id | same_location_count | same_location_store_id
SR Restaurant and Cafe   | -41.575449  | 147.16824 | 1112     | 0           | null
Big Bite Burgers         | -41.575449  | 147.16824 | 1113     | 0           | null
Amigos                   | -41.575449  | 147.16824 | 1114     | 0           | null
Domino's                 | -38.33983   | 143.58384 | 1115     | 0           | null

To this:

store_name               | latitude    | longitude | store_id | same_location_count | same_location_store_id
SR Restaurant and Cafe   | -41.575449  | 147.16824 | 1112     | 2           | 1113:1114
Big Bite Burgers         | -41.575449  | 147.16824 | 1113     | 2           | 1112:1114
Amigos                   | -41.575449  | 147.16824 | 1114     | 2           | 1112:1113
Domino's                 | -38.33983   | 143.58384 | 1115     | 0           | null

Here is the select statement that works for me:

SELECT
  *,
  (COUNT(id) OVER (PARTITION BY lat, long))-1 AS same_location_count,
  REPLACE( STRING_AGG(CONCAT(id,':'),'') OVER (PARTITION BY lat, long), CONCAT(id,':'), '') AS same_location_store_id
FROM
  test

Now, to the next step, how will I be able to update the table with those selected data?

CodePudding user response：

You can try with UPDATE FROM

update T set same_location_count = tx.same_location_count, same_location_store_id = tx.same_location_store_id
from test t
join (
    SELECT
      *,
      (COUNT(id) OVER (PARTITION BY lat, long))-1 AS same_location_count,
      REPLACE( STRING_AGG(CONCAT(id,':'),'') OVER (PARTITION BY lat, long), CONCAT(id,':'), '') AS same_location_store_id
    FROM
      test
) tx on t.id = tx.id