0

How to update names of the settlement that the first one will be "Castle 01", the next "Castle 02" and so on. The type of column village is varchar.

Something like this:

update settlement
set village = "Castle 01"  --next Castle 02, Castle 03,... Castle 12,...
zed
  • 3
  • 4
  • 1
    Ordered how? What are the other columns in the table? Post enough info to understand your question. – underscore_d May 06 '21 at 14:16
  • vid as primary key int, village varchar, x int, y int, population int, pid foreing key - reference table(player) – zed May 06 '21 at 14:25
  • Does this answer your question? [Auto Increment varchar in MySQL](https://stackoverflow.com/questions/14038586/auto-increment-varchar-in-mysql) – Ballard May 06 '21 at 14:28

2 Answers2

0

Below sample code where you can update your input in a loop and make changes respectivily

declare @input nvarchar(100);
declare @count int=10
while(@count<100)
begin
 set @input=CONCAT('Castle ',@count);
 print(@input)  
 set @count=@count +1;
end
Rahul Shukla
  • 646
  • 6
  • 19
0

You cannot auto-increment a varchar type column in mysql.

You can instead do one of the following:

  1. Lookup existing records and get a COUNT then concatenate your varchar village to have both castle and the value. Perhaps using:
SELECT count(*) FROM settlement WHERE village LIKE 'Castle%';
  1. Use two tables to define your settlement types and have an ID there instead, which will allow you to read your settlements back out with a join to then concatenate your real name.

If you do not do something similar to this on read/write then you'll keep running into having to update your column.

Ballard
  • 869
  • 11
  • 25