I add a new colum based on the values of another one. I'd like to update that new colum with the corrunt value plus an incremental number group by element. For example:

I try the following code but I always get a syntax error.

UPDATE cities as c
SET c.new_colum = concat(t.city,'-', t.col) 
FROM (SELECT ROW_NUMBER() OVER(partition by city) AS col, city_st FROM cities) t;   

CodePudding user response：

Assuming that you have added 'newcolumn' to cities and that there is a primary key in cities called 'id' then:

UPDATE cities c
JOIN
(SELECT id,
ROW_NUMBER() OVER(partition by city ) newcolumn
FROM cities) t on c.id=t.id
SET c.newcolumn = CONCAT(city,'-',t.newcolumn)
;

db-fiddle:

https://www.db-fiddle.com/f/dTnf3bNaXhEKKDzZzSED9K/0