SQL UPDATE: примеры обновления строк в таблице

Оператор UPDATE в SQL (Structured Query Language) позволяет изменять данные, хранящиеся в таблице базы данных. В данной документации мы рассмотрим, как использовать оператор UPDATE, а также предоставим примеры для лучшего понимания.

Синтаксис

Оператор UPDATE имеет следующий синтаксис:

UPDATE имя_таблицы
SET столбец1 = значение1, столбец2 = значение2, ...
WHERE условие;
  • UPDATE имя_таблицы: Указывает таблицу, в которой необходимо изменить данные.
  • SET столбец1 = значение1, столбец2 = значение2, ...: Задает новые значения для указанных столбцов.
  • WHERE условие: Опционально. Определяет условие, по которому будут выбраны строки для обновления. Если условие не указано, будут обновлены все строки.

Примеры

Предположим, у нас есть следующая таблица employees:

idfirst_namelast_namesalary
1ИванИванов50000
2ПетрПетров55000
3СергейСергеев60000
4АндрейАндреев65000

Пример 1: Обновление одного столбца

Допустим, мы хотим увеличить зарплату Ивана Иванова на 10%. Для этого используем следующий запрос:

UPDATE employees
SET salary = salary * 1.1
WHERE id = 1;

Результат обновления:

idfirst_namelast_namesalary
1ИванИванов55000
2ПетрПетров55000
3СергейСергеев60000
4АндрейАндреев65000

Пример 2: Обновление нескольких столбцов

Предположим, что мы хотим изменить имя и фамилию сотрудника с ID 4. Для этого используем следующий запрос:

UPDATE employees
SET first_name = 'Алексей', last_name = 'Алексеев'
WHERE id = 4;

Результат обновления:

idfirst_namelast_namesalary
1ИванИванов55000
2ПетрПетров55000
3СергейСергеев60000
4АлексейАлексеев65000

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

Предположим, что компания решила увеличить зарплату всем сотрудникам на 5%. Для этого используем следующий запрос:

UPDATE employees
SET salary = salary * 1.05;

Результат обновления:

idfirst_namelast_namesalary
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);

Результат обновления:

idfirst_namelast_namesalary
1ИванИванов63525
2ПетрПетров63525
3СергейСергеев63000
4АлексейАлексеев68250

Более сложные примеры

Теперь давайте перейдем к более сложным примерам и функциям, которые помогут вам освоить оператор UPDATE на более глубоком уровне.

Обновление данных с подзапросами

Иногда необходимо обновить данные в одной таблице на основе данных из другой таблицы. В этом случае можно использовать оператор JOIN. Рассмотрим следующий пример.

Пример 1: Обновление данных с использованием подзапросов

Предположим, у нас есть две таблицы: employees и departments.

Таблица employees:

idfirst_namelast_namedepartment_idsalary
1ИванИванов150000
2ПетрПетров255000
3СергейСергеев160000
4АндрейАндреев365000

Таблица departments:

iddepartment_namebonus
1IT10
2HR5
3Marketing15

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

UPDATE employees
SET salary = salary * (1 + (SELECT bonus FROM departments WHERE employees.department_id = departments.id) / 100);

Результат обновления:

idfirst_namelast_namedepartment_idsalary
1ИванИванов155000
2ПетрПетров257750
3СергейСергеев166000
4АндрейАндреев374750

В этом примере мы использовали подзапрос 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, и изменения станут видимыми для других пользователей. В случае ошибки в любом из запросов, транзакция откатится, и все изменения будут отменены.

Результат обновления:

idfirst_namelast_namedepartment_idsalary
1ИванИванов145000
2ПетрПетров267750
3СергейСергеев166000
4АндрейАндреев374750

Обновление данных с использованием условных выражений

Условные выражения позволяют выполнять различные операции обновления в зависимости от определенных условий.

Пример 3: Обновление данных с использованием CASE

Допустим, мы хотим увеличить зарплату сотрудникам с зарплатой ниже 60000 на 10%, а сотрудникам с зарплатой выше или равной 60000 на 5%. Для этого используем выражение CASE:

UPDATE employees
SET salary = CASE
    WHEN salary < 60000 THEN salary * 1.1
    ELSE salary * 1.05
END;

Результат обновления:

idfirst_namelast_namedepartment_idsalary
1ИванИванов149500
2ПетрПетров260500
3СергейСергеев163000
4АндрейАндреев368250

SQL Оператор UPDATE: оптимизация производительности, использование CTE и триггеров.

Обновление данных с использованием CTE (Common Table Expressions)

CTE предоставляют возможность создания временных таблиц для использования в одном запросе. Это позволяет упростить сложные запросы и улучшить их производительность.

Пример 1: Обновление данных с использованием CTE

Предположим, у нас есть таблица employees:

idfirst_namelast_namedepartment_idsalary
1ИванИванов150000
2ПетрПетров255000
3СергейСергеев160000
4АндрейАндреев365000

Мы хотим увеличить зарплату сотрудникам, имеющим зарплату ниже медианы. Сначала найдем медианное значение зарплаты с использованием 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);

Результат обновления:

idfirst_namelast_namedepartment_idsalary
1ИванИванов155000
2ПетрПетров260500
3СергейСергеев160000
4АндрейАндреев365000

Использование триггеров для контроля изменений

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

Пример 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 после обновления:

idemployee_idold_salarynew_salarychanged_at
1150000550002023-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 для решения сложных задач и сделать ваш код более эффективным.