To make the values in the column non-repeating (add numbers to the duplicate names), use the update set in the SQL query

Компьютер, программы

→

Microsoft SQL (Database Query Language)

Сделать значения в колонке неповторяющимися (добавляем к повторяющимся именам цифры) используем update set в SQL запросе

посмотрели 6246 раз

обновлено: 8 February 2019

Таблица Book

Name

Price

aaa

bbb

aaa

bbb

ooo

Вариант 1. SQL запрос

update Book
set Name = Name + ' ' + CAST( (SELECT count(*) FROM Book b WHERE Book.Name = b.Name AND Book.Id > b.Id) as VARCHAR(255) )

Результат SQL запроса

Name

Price

aaa 0

aaa 1

bbb 0

aaa 2

aaa 3

bbb 1

ooo 0

Внимание! Запрос можно выполнять только 1 раз.
Если запрос выполнить 2 раза то будет
aaa 0 0
aaa 1 0
bbb 0 0
и т.д.

Вариант 2. SQL запрос

update Book
set Name = Name + ' ' + CAST( (SELECT count(*) FROM Book b WHERE Book.Name = b.Name AND Book.Id > b.Id) as VARCHAR(255) )
where Id in (SELECT b1.Id FROM Book b1, Book b2 WHERE b1.Name = b2.Name AND b1.ID > b2.ID)

Результат SQL запроса

Name

Price

aaa

aaa 1

bbb

aaa 2

aaa 3

bbb 1

ooo

Внимание! Этот запрос можно выполнять много раз.

← Previous topic
inner join, left join, right join, outter join in a SQL query

Next topic →
How to use the SQL query to split a string into words using the delimiter in SQL Server ? Creating a Stored Function in SQL Server Management Studio

Your feedback ... Comments ...

Объявления

SQL Examples

• Select projects where the number of tasks in the project is greater than or equal to 3 (Use group by, having count in a SQL query)

• inner join, left join, right join, outter join in a SQL query

• To make the values in the column non-repeating (add numbers to the duplicate names), use the update set in the SQL query

• How to use the SQL query to split a string into words using the delimiter in SQL Server ? Creating a Stored Function in SQL Server Management Studio

Simplify complex joins and subqueries in SQL

• Common Table Expressions (CTE) for simplify complex joins and subqueries in SQL

SQL Optimization

• Optimization, improvement of SQL queries