Обновление null значений в SQL на уникальные числа

Быстрый ответ

Чтобы обновить столбец целочисленного типа в таблице, используйте функцию ROW_NUMBER(). Вот пример кода:

UPDATE t
SET t.`InterfaceID` = r.RowNum
FROM `YourTable` t
JOIN (
  SELECT `PrimaryKey`, ROW_NUMBER() OVER (ORDER BY `OrderColumn`) AS RowNum
  FROM `YourTable`
) r
ON t.`PrimaryKey` = r.`PrimaryKey`;

В данном примере необходимо заменить InterfaceID, YourTable, PrimaryKey и OrderColumn на наименования соответствующих колонок и таблиц из вашей базы данных.

Практическое руководство

Иногда требуется работа с SQL-переменной для присвоения последовательных значений. Это актуально в случаях, когда необходимо начать с определенного числа или когда в столбце InterfaceID присутствуют значения NULL:

SET @newId := (SELECT MAX(`InterfaceID`) FROM `YourTable`);
UPDATE `YourTable` SET `InterfaceID` = (@newId := @newId + 1) WHERE `InterfaceID` IS NULL;

Важно контролировать начальное значение переменной @newId, чтобы избежать конфликтов между идентификаторами.

Продвинутые методы обновления

Поиск и заполнение пропусков

Вы можете использовать функцию ROW_NUMBER(), рассчитывая смещение, чтобы заполнить пропуски в последовательности ID:

WITH NumberedRows AS (
  SELECT `InterfaceID`, 
      ROW_NUMBER() OVER (ORDER BY `OrderColumn`) + (SELECT MAX(`InterfaceID`) FROM `YourTable`) AS NewID
  FROM `YourTable`
  WHERE `InterfaceID` IS NULL
)
UPDATE `YourTable`
SET `InterfaceID` = NumberedRows.NewID
FROM NumberedRows
WHERE `YourTable`.`PrimaryKey` = NumberedRows.`PrimaryKey`;

Таким образом, присвоение новых значений InterfaceID начнется со следующего числа после максимального.

Подробнее об этом расскажет наш спикер на видео

Self-join для обновления по положению

Self-join — это эффективный метод обновления строк в зависимости от их позиции в наборе данных:

UPDATE t1
SET t1.`InterfaceID` = t2.RowNum
FROM `YourTable` t1
JOIN (
  SELECT `PrimaryKey`, ROW_NUMBER() OVER (ORDER BY `OrderColumn`) AS RowNum
  FROM `YourTable` 
  WHERE `InterfaceID` IS NULL
) t2
ON t1.`PrimaryKey` = t2.`PrimaryKey`;

А для Postgres используются типы SERIAL или SEQUENCE для автоматического инкрементирования значений поля.

Визуализация

Процесс обновления в SQL можно сравнить с решением головоломки на парковке:

Первоначально:

🚗=1, 🚗=1, 🚗=1, 🚗=1

После исполнения:

SET @rank := 0;
UPDATE cars SET parking_space = (@rank := @rank + 1);

Итоговый результат:

🚗=1, 🚗=2, 🚗=3, 🚗=4

Таким образом, каждому автомобилю (или строке) после обновления присваивается уникальное место парковки (или уникальный ID).

Навигация по сложным обновлениям

Нелинейные приращения и специфические условия

Если вы столкнулись со специфическими условиями или изменяемыми приращениями, воспользуйтесь конструкцией CASE:

SET @rank := 0;
UPDATE `YourTable` 
SET `InterfaceID` = CASE
  WHEN Condition THEN @rank := @rank + CustomIncrement
  ELSE @rank := @rank + 1
END
WHERE `InterfaceID` IS NULL;

В этом случае Condition – это условия, а CustomIncrement – размер приращения.

Уважение к ограничениям первичного ключа

При обновлении уникальности первичного ключа следует избегать дубликатов.

Свобода ORDER BY

Используя ORDER BY в ROW_NUMBER(), можно контролировать порядок данных при их обновлении.

Полезные материалы

1. MySQL :: Руководство по MySQL 8.0 :: 5.6.9 Использование AUTO_INCREMENT — подробная информация о AUTO_INCREMENT в MySQL.
2. PostgreSQL: Документация: 16: 8.1. Числовые типы — сведения о SERIAL и SEQUENCE в PostgreSQL.
3. ROW_NUMBER (Transact-SQL) – SQL Server | Microsoft Learn — информация о ROW_NUMBER() представленная Microsoft.
4. IBM Документация — подробно о создании секвенций в DB2.