SQLite UPDATE: обновление данных в таблице

Однажды я потратил полчаса на поиск бага, который оказался банальной опечаткой в одной строке таблицы. Именно тогда я по-настоящему оценил оператор UPDATE — он позволяет точечно исправлять ошибки, менять форматы и приводить записи в порядок без лишних операций. В этом руководстве мы детально разберём синтаксис SQLite UPDATE и пройдём по практическим примерам от простого к сложному

Синтаксис оператора SQLite UPDATE

Для обновления существующих данных в таблице используется оператор SQLite UPDATE. Базовый синтаксис выглядит так:

UPDATE table
SET column_1 = new_value_1, column_2 = new_value_2
WHERE search_condition
ORDER column_or_expression
LIMIT row_count OFFSET offset;

Разберём каждую часть по порядку

Во-первых, после ключевого слова UPDATE указывается таблица, данные которой нужно обновить

Секция SET задаёт новое значение для каждого столбца. Можно обновлять один столбец или несколько сразу — через запятую

Условие в секции WHERE определяет, какие строки необходимо обновить. Если эта секция не указана, изменения будут применены ко всем строкам таблицы, что может привести к неожиданным результатам

Если в секции LIMIT указать отрицательное значение, SQLite трактует это как отсутствие ограничений и обновляет все строки, удовлетворяющие условию в секции WHERE

Наконец, секции ORDER BY и LIMIT позволяют точно указать количество обновляемых строк

Секция ORDER BY должна применяться вместе с секцией LIMIT, чтобы точно указать, какие строки будут обновлены. Без ORDER BY порядок строк в таблице не определён, и предсказать, какая именно строка будет обновлена, невозможно

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы

Учебная база данных для примеров

Для практической демонстрации использования оператора UPDATE возьмём таблицу employees из учебной базы данных. Ниже приведён оператор SELECT, который извлекает данные из этой таблицы:

SELECT employeeid, firstname, lastname, title, email
FROM employees;

Именно с этими данными мы будем работать во всех примерах ниже

Примеры оператора SQLite UPDATE

Обновление одного столбца

Предположим, Джейн вышла замуж и хочет сменить фамилию на фамилию мужа — Smith. В этом случае запись Джейн обновляется следующим оператором:

UPDATE employees
SET lastname = 'Smith'
WHERE employeeid = 3;

Условие в секции WHERE гарантирует, что будет обновлена только запись Джейн. Столбец lastname получает строковый литерал (string literal) 'Smith'

Для проверки результата используйте следующий оператор:

SELECT employeeid, firstname, lastname, title, email
FROM employees
WHERE employeeid = 3;

Обновление нескольких столбцов

Предположим, Парк Маргарет переехала в Торонто, и нужно изменить её адрес, город и почтовый индекс. Оператор UPDATE позволяет обновить несколько столбцов одновременно:

UPDATE employees
SET city = 'Toronto', state = 'ON', postalcode = 'M5P 2N7'
WHERE employeeid = 4;

SELECT employeeid, firstname, lastname, state, city, PostalCode
FROM employees
WHERE employeeid = 4;

Обновление с секциями ORDER BY и LIMIT

Для использования оператора UPDATE с секциями ORDER BY и LIMIT убедитесь, что SQLite собран с параметром SQLITE_ENABLE_UPDATE_DELETE_LIMIT

Сначала проверим адреса электронной почты сотрудников в таблице employees:

SELECT employeeid, firstname, lastname, email
FROM employees;

Чтобы обновить только одну строку, используйте LIMIT 1. Для того чтобы гарантировать, что изменится именно первая строка из списка сотрудников, отсортированных по имени, добавьте ORDER BY firstname

Следующий оператор обновляет электронную почту Эндрю Адамса (Andrew Adams):

UPDATE employees
SET email = LOWER(firstname || "." || lastname || "@chinookcorp.com")
ORDER BY firstname
LIMIT 1;

Новый адрес электронной почты формируется из имени, точки (.), фамилии и суффикса @chinookcorp.com, а функция LOWER() преобразует весь адрес в нижний регистр

Обновление всех строк таблицы

Чтобы обновить все строки в таблице employees, достаточно опустить секцию WHERE. Например, следующий оператор приводит адреса электронной почты всех сотрудников к нижнему регистру:

UPDATE employees
SET email = LOWER(firstname || "." || lastname || "@chinookcorp.com");

Я рекомендую всегда дважды проверять запрос перед выполнением без WHERE — особенно в боевых базах данных, где откат изменений может быть нетривиальным

Типичные ошибки при работе с UPDATE

На практике большинство проблем с оператором UPDATE связано с несколькими повторяющимися ситуациями

Забытая секция WHERE. Самая распространённая ошибка — запустить UPDATE ... SET ... без условия и обновить все строки таблицы вместо одной. Перед выполнением UPDATE полезно сначала написать аналогичный SELECT с тем же условием и убедиться, что он возвращает именно те строки, которые нужно изменить

Неверное условие в WHERE. Если условие слишком широкое, под обновление попадут лишние строки. Если слишком узкое — нужные строки не будут затронуты. Проверяйте условие отдельным SELECT до запуска UPDATE

Использование ORDER BY без LIMIT. Секция ORDER BY в операторе UPDATE имеет смысл только в паре с LIMIT. Без LIMIT она не влияет на результат и может ввести в заблуждение при чтении кода

Отсутствие поддержки SQLITE_ENABLE_UPDATE_DELETE_LIMIT. Если SQLite собран без этого параметра, попытка использовать ORDER BY и LIMIT в UPDATE завершится ошибкой. Убедитесь, что ваша сборка поддерживает эту возможность, прежде чем полагаться на неё в коде

Обновление не того столбца. При обновлении нескольких столбцов легко перепутать имена или значения. Сверяйте схему таблицы перед написанием запроса

Проверка результата после UPDATE

Хорошая практика — всегда проверять результат UPDATE отдельным запросом SELECT. Это особенно важно при обновлении нескольких столбцов или при использовании сложных условий в WHERE

Общий шаблон проверки выглядит так:

-- Сначала проверяем, что SELECT возвращает нужные строки
SELECT employeeid, firstname, lastname, email
FROM employees
WHERE employeeid = 3;

-- Затем выполняем UPDATE
UPDATE employees
SET email = 'new_email@example.com'
WHERE employeeid = 3;

-- И снова проверяем результат
SELECT employeeid, firstname, lastname, email
FROM employees
WHERE employeeid = 3;

На мой взгляд, этот шаблон стоит превратить в привычку: он занимает секунды, но страхует от ошибок, которые потом сложно отследить. Я всегда придерживаюсь этого подхода при работе с данными, которые сложно восстановить вручную

Ответы на эти вопросы могут быть для вас полезными

Что произойдёт, если в операторе UPDATE не указать секцию WHERE?

Оператор обновит все строки таблицы без исключения. Это стандартное поведение SQLite — условие WHERE является необязательным, и его отсутствие не вызывает ошибки

Можно ли обновить несколько столбцов одним оператором UPDATE?

Да. В секции SET перечислите столбцы через запятую: SET column_1 = value_1, column_2 = value_2. Все изменения применятся в рамках одного оператора

Как ограничить количество обновляемых строк?

Используйте секцию LIMIT совместно с ORDER BY. Например, ORDER BY firstname LIMIT 1 обновит только одну строку — первую в алфавитном порядке по имени. Для этого SQLite должен быть собран с параметром SQLITE_ENABLE_UPDATE_DELETE_LIMIT

Как убедиться, что UPDATE обновил нужные строки?

Выполните SELECT с тем же условием WHERE до и после UPDATE. Это позволит сравнить значения и убедиться, что изменения применились корректно

Можно ли использовать функции в секции SET?

Да. SQLite поддерживает использование встроенных функций непосредственно в SET. Например, SET email = LOWER(firstname || "." || lastname || "@example.com") вычислит новое значение на лету при выполнении запроса