Оператор UPDATE в SQL (Structured Query Language) позволяет изменять данные, хранящиеся в таблице базы данных. В данной документации мы рассмотрим, как использовать оператор UPDATE, а также предоставим примеры для лучшего понимания.
Синтаксис
Оператор UPDATE имеет следующий синтаксис:
UPDATE имя_таблицы SET столбец1 = значение1, столбец2 = значение2, ... WHERE условие;
- UPDATE имя_таблицы: Указывает таблицу, в которой необходимо изменить данные.
- SET столбец1 = значение1, столбец2 = значение2, ...: Задает новые значения для указанных столбцов.
- WHERE условие: Опционально. Определяет условие, по которому будут выбраны строки для обновления. Если условие не указано, будут обновлены все строки.
Примеры
Предположим, у нас есть следующая таблица employees:
| id | first_name | last_name | salary |
|---|---|---|---|
| 1 | Иван | Иванов | 50000 |
| 2 | Петр | Петров | 55000 |
| 3 | Сергей | Сергеев | 60000 |
| 4 | Андрей | Андреев | 65000 |
Пример 1: Обновление одного столбца
Допустим, мы хотим увеличить зарплату Ивана Иванова на 10%. Для этого используем следующий запрос:
UPDATE employees SET salary = salary * 1.1 WHERE id = 1;
Результат обновления:
| id | first_name | last_name | salary |
|---|---|---|---|
| 1 | Иван | Иванов | 55000 |
| 2 | Петр | Петров | 55000 |
| 3 | Сергей | Сергеев | 60000 |
| 4 | Андрей | Андреев | 65000 |
Пример 2: Обновление нескольких столбцов
Предположим, что мы хотим изменить имя и фамилию сотрудника с ID 4. Для этого используем следующий запрос:
UPDATE employees SET first_name = 'Алексей', last_name = 'Алексеев' WHERE id = 4;
Результат обновления:
| id | first_name | last_name | salary |
|---|---|---|---|
| 1 | Иван | Иванов | 55000 |
| 2 | Петр | Петров | 55000 |
| 3 | Сергей | Сергеев | 60000 |
| 4 | Алексей | Алексеев | 65000 |
Пример 3: Обновление всех строк таблицы
Предположим, что компания решила увеличить зарплату всем сотрудникам на 5%. Для этого используем следующий запрос:
UPDATE employees SET salary = salary * 1.05;
Результат обновления:
| id | first_name | last_name | salary |
|---|---|---|---|
| 1 | Иван | Иванов | 57750 |
| 2 | Петр | Петров | 57750 |
| 3 | Сергей | Сергеев | 63000 |
| 4 | Алексей | Алексеев | 68250 |
Пример 4: Обновление строк с использованием подзапросов
Допустим, мы хотим увеличить зарплату сотрудникам с зарплатой ниже средней. Сначала вычислим среднюю зарплату:
SELECT AVG(salary) FROM employees;
Предположим, что средняя зарплата равна 61625. Теперь увеличим зарплату на 10% для сотрудников с зарплатой ниже средней:
UPDATE employees SET salary = salary * 1.1 WHERE salary < (SELECT AVG(salary) FROM employees);
Результат обновления:
| id | first_name | last_name | salary |
|---|---|---|---|
| 1 | Иван | Иванов | 63525 |
| 2 | Петр | Петров | 63525 |
| 3 | Сергей | Сергеев | 63000 |
| 4 | Алексей | Алексеев | 68250 |
Более сложные примеры
Теперь давайте перейдем к более сложным примерам и функциям, которые помогут вам освоить оператор UPDATE на более глубоком уровне.
Обновление данных с подзапросами
Иногда необходимо обновить данные в одной таблице на основе данных из другой таблицы. В этом случае можно использовать оператор JOIN. Рассмотрим следующий пример.
Пример 1: Обновление данных с использованием подзапросов
Предположим, у нас есть две таблицы: employees и departments.
Таблица employees:
| id | first_name | last_name | department_id | salary |
|---|---|---|---|---|
| 1 | Иван | Иванов | 1 | 50000 |
| 2 | Петр | Петров | 2 | 55000 |
| 3 | Сергей | Сергеев | 1 | 60000 |
| 4 | Андрей | Андреев | 3 | 65000 |
Таблица departments:
| id | department_name | bonus |
|---|---|---|
| 1 | IT | 10 |
| 2 | HR | 5 |
| 3 | Marketing | 15 |
Теперь мы хотим увеличить зарплату каждому сотруднику на основе процентного бонуса их отдела. Для этого используем следующий запрос:
UPDATE employees SET salary = salary * (1 + (SELECT bonus FROM departments WHERE employees.department_id = departments.id) / 100);
Результат обновления:
| id | first_name | last_name | department_id | salary |
|---|---|---|---|---|
| 1 | Иван | Иванов | 1 | 55000 |
| 2 | Петр | Петров | 2 | 57750 |
| 3 | Сергей | Сергеев | 1 | 66000 |
| 4 | Андрей | Андреев | 3 | 74750 |
В этом примере мы использовали подзапрос SELECT
Обновление данных с использованием транзакций
Транзакции позволяют выполнять несколько операций обновления одновременно и гарантируют их атомарность. В случае ошибки транзакция откатывается, и все изменения отменяются.
Пример 2: Обновление данных с использованием транзакций
Допустим, мы хотим перевести 10000 единиц зарплаты от сотрудника с ID 1 к сотруднику с ID 2. Для этого используем транзакцию:
BEGIN TRANSACTION; UPDATE employees SET salary = salary - 10000 WHERE id = 1; UPDATE employees SET salary = salary + 10000 WHERE id = 2; COMMIT;
Если оба запроса UPDATE выполнены успешно, транзакция будет завершена командой COMMIT, и изменения станут видимыми для других пользователей. В случае ошибки в любом из запросов, транзакция откатится, и все изменения будут отменены.
Результат обновления:
| id | first_name | last_name | department_id | salary |
|---|---|---|---|---|
| 1 | Иван | Иванов | 1 | 45000 |
| 2 | Петр | Петров | 2 | 67750 |
| 3 | Сергей | Сергеев | 1 | 66000 |
| 4 | Андрей | Андреев | 3 | 74750 |
Обновление данных с использованием условных выражений
Условные выражения позволяют выполнять различные операции обновления в зависимости от определенных условий.
Пример 3: Обновление данных с использованием CASE
Допустим, мы хотим увеличить зарплату сотрудникам с зарплатой ниже 60000 на 10%, а сотрудникам с зарплатой выше или равной 60000 на 5%. Для этого используем выражение CASE:
UPDATE employees
SET salary = CASE
WHEN salary < 60000 THEN salary * 1.1
ELSE salary * 1.05
END;
Результат обновления:
| id | first_name | last_name | department_id | salary |
|---|---|---|---|---|
| 1 | Иван | Иванов | 1 | 49500 |
| 2 | Петр | Петров | 2 | 60500 |
| 3 | Сергей | Сергеев | 1 | 63000 |
| 4 | Андрей | Андреев | 3 | 68250 |
SQL Оператор UPDATE: оптимизация производительности, использование CTE и триггеров.
Обновление данных с использованием CTE (Common Table Expressions)
CTE предоставляют возможность создания временных таблиц для использования в одном запросе. Это позволяет упростить сложные запросы и улучшить их производительность.
Пример 1: Обновление данных с использованием CTE
Предположим, у нас есть таблица employees:
| id | first_name | last_name | department_id | salary |
|---|---|---|---|---|
| 1 | Иван | Иванов | 1 | 50000 |
| 2 | Петр | Петров | 2 | 55000 |
| 3 | Сергей | Сергеев | 1 | 60000 |
| 4 | Андрей | Андреев | 3 | 65000 |
Мы хотим увеличить зарплату сотрудникам, имеющим зарплату ниже медианы. Сначала найдем медианное значение зарплаты с использованием CTE:
WITH salary_median AS (
SELECT AVG(salary) AS median
FROM (
SELECT salary
FROM employees
ORDER BY salary
LIMIT 2 OFFSET (SELECT COUNT(*) / 2 - 1 FROM employees)
) sub
)
UPDATE employees
SET salary = salary * 1.1
WHERE salary < (SELECT median FROM salary_median);
Результат обновления:
| id | first_name | last_name | department_id | salary |
|---|---|---|---|---|
| 1 | Иван | Иванов | 1 | 55000 |
| 2 | Петр | Петров | 2 | 60500 |
| 3 | Сергей | Сергеев | 1 | 60000 |
| 4 | Андрей | Андреев | 3 | 65000 |
Использование триггеров для контроля изменений
Триггеры позволяют выполнять определенные действия при изменении данных в таблице. Используя триггеры, можно логировать изменения или контролировать правила обновления.
Пример 2: Создание триггера для логирования изменений зарплаты
Предположим, мы хотим сохранять историю изменений зарплаты для каждого сотрудника. Сначала создадим таблицу salary_history:
CREATE TABLE salary_history ( id SERIAL PRIMARY KEY, employee_id INTEGER, old_salary INTEGER, new_salary INTEGER, changed_at TIMESTAMP );
Теперь создадим триггер, который будет записывать изменения зарплаты в таблицу salary_history:
CREATE OR REPLACE FUNCTION log_salary_update() RETURNS TRIGGER AS $$ BEGIN INSERT INTO salary_history (employee_id, old_salary, new_salary, changed_at) VALUES (OLD.id, OLD.salary, NEW.salary, NOW()); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER salary_update_trigger AFTER UPDATE OF salary ON employees FOR EACH ROW EXECUTE FUNCTION log_salary_update();
Теперь, когда мы обновим зарплату сотрудника, триггер автоматически добавит запись в таблицу salary_history:
UPDATE employees SET salary = salary * 1.1 WHERE id = 1;
Таблица salary_history после обновления:
| id | employee_id | old_salary | new_salary | changed_at |
|---|---|---|---|---|
| 1 | 1 | 50000 | 55000 | 2023-03-21 12:34:56 |
Оптимизация производительности при обновлении данных
При работе с большими объемами данных, производительность обновлений может стать критическим фактором. Важно минимизировать время блокировки таблиц и количество записей, которые обновляются.
Пример 3: Обновление данных с использованием индексов
Предположим, у нас есть таблица employees с большим количеством записей, и мы хотим увеличить зарплату сотрудникам с определенным department_id. В этом случае, индекс по department_id может значительно ускорить операцию:
CREATE INDEX employees_department_id_index ON employees(department_id);
Теперь выполним запрос обновления:
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 2;
Использование индекса позволяет быстрее найти записи, которые необходимо обновить, и сократить время выполнения запроса.
Мы рассмотрели использование оператора UPDATE с CTE, триггерами и оптимизацией производительности. Эти темы помогут вам использовать оператор UPDATE для решения сложных задач и сделать ваш код более эффективным.