CODEWITHSUNDEEP
mysql
dbj44
dbj44

Reputation: 1998

Update table column so that each row has a string plus incrementing number

Is there a SQL command to update a column in a table, something like:

UPDATE user_data
SET name='user' + ' ' + n

so that each row is updated with a string plus an incrementing number?

name
------
user 1
user 2
user 3

Upvotes: 0

Views: 29

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562558

Do it in two statements:

SET @n = 0;

UPDATE user_data
SET name = CONCAT('user', ' ', (@n:=@n+1));

You could even combine 'user' and ' ' into one string, but I showed it as separate to make the point that CONCAT() takes any number of arguments.

By the way, + is a string concatenation operator in Microsoft SQL Server and Microsoft Access. Not in standard SQL, nor in MySQL.

Upvotes: 1

Related Questions