Изменение данных

Вставка записей

Оператор INSERT используется для добавления новых строк в таблицу. Он позволяет вставлять данные в одну или несколько строк, а также поддерживает вставку данных из других таблиц с помощью подзапросов. Рассмотрим основные варианты использования INSERT.

Явное указание значений

INSERT INTO <таблица> [(<столбцы>)] VALUES(<значения>)

Вставляет строку в таблицу с указанными значениями. В списке VALUES должны быть указаны значения всех столбцов из списка столбцов в соответствующем порядке. Если список столбцов отсутствует, должны быть указаны все столбцы таблицы кроме вычисляемых.

Например, добавим новый проект в таблицу PROJECT, указав все необходимые поля.

INSERT INTO PROJECT VALUES ('RDB', 'RedDatabase', 'The Best Relation Database Management System', NULL, 'software')

Последнее поле — это поле по умолчанию. Его можно было не указывать. Но тогда необходимо указать список вставляемых столбцов. Также можно не указывать поля, обозначенные NOT NULL. Например,

INSERT INTO PROJECT (PROJ_ID, PROJ_NAME) VALUES ('RDB', 'RedDatabase')

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

Можно вставить данные в таблицу из результата подзапроса.

INSERT INTO <таблица> [(<столбцы>)] SELECT <список выбора> ...

В этом случае выходные столбцы оператора SELECT, должны предоставить значения для каждого целевого столбца в списке столбцов, в том же порядке и совместимого типа. Если список столбцов отсутствует, то значения должны быть предоставлены для каждого столбца таблицы или представления (исключая вычисляемые столбцы).

Например, создадим новую таблицу SOFTWARE_PROJECT и вставим в нее все проекты из таблицы PROJECT, где тип продукта software.

CREATE TABLE SOFTWARE_PROJECT (
    PROJ_ID PROJNO NOT NULL,
    PROJ_NAME VARCHAR(20) NOT NULL,
    PROJ_DESC BLOB SUB_TYPE TEXT SEGMENT SIZE 800,
    TEAM_LEADER EMPNO,
    PRODUCT PRODTYPE);

INSERT INTO SOFTWARE_PROJECT SELECT * FROM PROJECT WHERE PRODUCT='software';

Аналогично вставке значений, можно использовать не все поля.

INSERT INTO SOFTWARE_PROJECT (PROJ_ID, PROJ_NAME) 
    SELECT P.PROJ_ID, P.PROJ_NAME FROM PROJECT P WHERE PRODUCT='software'

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

Добавление строки со значениями по умолчанию

Предложение DEFAULT VALUES позволяет вставлять записи без указания значений вообще.

INSERT INTO <таблица> DEFAULT VALUES

Это возможно, только все поля NOT NULL, имеют значения по умолчанию, или эти значения устанавливаются в BEFORE INSERT триггере.

Например:

INSERT INTO JOURNAL DEFAULT VALUES

Получение вставленных значений

Оператор INSERT вставляющий только одну строку может включать необязательное предложение RETURNING, которое позволяет вернуть данные вставленной строки. Это полезно, например, для получения значения автоматически генерируемого первичного ключа.

INSERT ... [RETURNING <возвращаемый список выбора>]

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

Возвращаемые значения содержат все изменения, произведённые в триггерах BEFORE.

Например, создадим таблицу с автоматически генерируемым первичным ключом.

CREATE TABLE EVENT (
    EVENT_ID INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    EVENT_DESCRIPTION VARCHAR(100)
)

Теперь допустим, что в программе нам нужно вставить новую запись и получить значений сгенерированного значения поля EVENT_ID новой записи.

INSERT INTO EVENT (EVENT_DESCRIPTION) VALUES ('Power On') RETURNING EVENT_ID

Этот запрос выполниться как SELECT и вернет одну запись с полем EVENT_ID.

EVENT_ID
20

Обновление записей

Оператор UPDATE используется для изменения существующих данных в таблице. Он позволяет обновлять значения в одной или нескольких строках на основе заданных условий. Рассмотрим основные варианты использования UPDATE.

UPDATE <таблица> SET поле = <новое значение> 
                  [, поле = <новое значение> ...]
    [WHERE <условие поиска>]
    [RETURNING <возвращаемый список выбора>]

Изменяемые столбцы

Изменяемые столбцы указываются в предложении SET. Столбцы и их значения перечисляются через запятую. Слева имя столбца, а справа значение или выражение.

Разрешено использовать имена столбцов в выражениях справа. При этом использоваться будет всегда старое значение столбца, даже если присваивание этому столбцу уже произошло ранее в перечислении SET. Один столбец может быть использован слева только один раз в конструкции SET.

Допустим у нас есть таблица T:

AB
10
20

После выполнения оператора

UPDATE T SET A = 5, B = A
AB
51
52

Обратите внимание, что старые значения 1 и 2 используются для обновления столбца B, даже после того как столбцу A были назначено новое значение 5.

Ограничение записей

Предложение WHERE ограничивает набор обновляемых записей заданным условием. Формат, синтаксис и смысл данной секции совпадает с одноименной секцией оператора SELECT.

Рассмотрим несколько примеров.

UPDATE ADDRESSES
    SET CITY = 'Saint Peterburg', CITYCODE = 'PET'
    WHERE CITY = 'Leningrad'

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

UPDATE EMPLOYEES
    SET SALARY = 2.5 * SALARY
    WHERE TITLE = 'CEO'

Здесь мы использовали значение поля SALARY для того, чтобы вычислить новое значение этого же поля.

UPDATE EMPLOYEE E
    SET SALARY = SALARY * 1.05
    WHERE EXISTS(SELECT * FROM EMPLOYEE_PROJECT EP WHERE E.EMP_NO = EP.EMP_NO);

В данном примере всем сотрудникам, которые заняты в каком-либо проекте, мы увеличиваем зарплату на 5%.

Выражение CASE

Выражение CASE можно использовать для условного обновления данных.

Например, увеличим зарплату сотрудникам в зависимости от отдела:

UPDATE EMPLOYEE
SET SALARY = CASE
    WHEN DEPT_NO = '000' THEN SALARY - 50
    WHEN DEPT_NO = '621' THEN SALARY - 70
    ELSE SALARY - 30
END

Получение обновленных значений

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

UPDATE employee
  SET salary = salary * 1.1
  RETURNING salary

Вставка или обновление записей

Если мы не знаем, существует ли запись, которую нужно обновить, или её требуется добавить, удобно использовать оператор UPDATE OR INSERT.

Его синтаксис:

UPDATE OR INSERT INTO <таблица> [(столбцы>)]
    VALUES (<значения>)
    [MATCHING (<столбцы>)]
    [RETURNING <возвращаемый список выбора>]

Что именно произойдёт, вставка или обновление, зависит от значений столбцов в предложении MATCHING, а в случае, если оно не указано, то от значений столбцов первичного ключа. Если найдены записи, совпадающие с указанными значениями, то они обновятся, а иначе будет добавлена новая запись.

Когда у таблицы нет первичного ключа, то обязательно надо указать MATCHING.

Предложение RETURNING используется так же как и в операторах UPDATE или INSERT.

Пример запроса:

UPDATE OR INSERT INTO PROJECT 
    VALUES ('RDB', 'RedDatabase', 'The Best Relation Database Management System', NULL, 'software')
    MATCHING (PROJ_ID)

Удаление записей

Оператор DELETE в SQL используется для удаления одной или нескольких строк из таблицы. DELETE позволяет удалять данные на основе условий, указанных в предложении WHERE. Если условие не указано, удаляются все строки из таблицы.

Синтаксис оператора DELETE:

DELETE FROM target [[AS] <псевдоним>]
    [WHERE <условие поиска>]
    [RETURNING <возвращаемый список выбора>]

Удалим все записи из таблицы employee:

DELETE FROM EMPLOYEE

Чаще всего DELETE используется с условием, чтобы удалить только определенные строки.

Например, удалим сотрудника с emp_no = 5:

DELETE FROM EMPLOYEE WHERE EMP_NO = 5

Можно использовать подзапросы для удаления данных на основе другой таблицы.

Например, удалим всех сотрудников, которые работают в отделах, расположенных в Бостоне:

DELETE FROM EMPLOYEE WHERE DEPT_NO IN 
    (SELECT DEPT_NO FROM DEPARTMENT WHERE LOCATION = 'Boston')

Предложение RETURNING позволяет выбрать данные удаленных записей.

Например, удалим сотрудника с emp_no = 3 и получим его данные:

DELETE FROM EMPLOYEE WHERE EMP_NO = 3
    RETURNING EMP_NO, FIRST_NAME, LAST_NAME;

Если таблица связана с другими таблицами через внешние ключи с опцией ON DELETE CASCADE, удаление строки из родительской таблицы приведет к автоматическому удалению связанных строк из дочерних таблиц.

Предположим, что у нас есть таблица PROJECTS, связанная с таблицей EMPLOYEE через внешний ключ:

CREATE TABLE PROJECTS (
    PROJECT_ID INT PRIMARY KEY,
    PROJECT_NAME VARCHAR(100),
    EMP_NO INT,
    FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE(EMP_NO) ON DELETE CASCADE
)

Удаление сотрудника из таблицы EMPLOYEE автоматически удалит все его проекты из таблицы PROJECTS:

DELETE FROM EMPLOYEE WHERE EMP_NO = 1;

Слияние наборов данных

Оператор MERGE производит слияние записей источника в целевую таблицу. Источником может быть таблица, представление, хранимая процедура или производная таблица. Каждая запись источника используется для обновления (предложение UPDATE) или удаления (предложение DELETE) одной или более записей цели, или вставки (предложение INSERT) записи в целевую таблицу, или ни для того, ни для другого. Условие обычно содержит сравнение столбцов в таблицах источника и цели.

Синтаксис оператора MERGE:

MERGE INTO <целевая таблица> [[AS] <псевдоним>]
    USING <источник> [[AS] псевдоним] 
    ON <условие соединения> 
    [WHEN MATCHED [ AND <условие> ]
        THEN { UPDATE SET <присвоение новых значение> | DELETE }]
    ...
    [WHEN NOT MATCHED [BY TARGET] [ AND <условие> ]
        THEN INSERT [ (<список столбцов>) ] VALUES (<список значений>)]
    ...
    [WHEN NOT MATCHED BY SOURCE [ AND <условие> ]
        THEN { UPDATE SET <присвоение новых значение> | DELETE }]
    ...
    [RETURNING <возвращаемый список выбора>]

В списке VALUES предложения INSERT и списке SET предложения UPDATE вместо значения столбца можно использовать ключевое слово DEFAULT. В этом случае столбец получит значение по умолчанию, указанное при определении целевой таблицы. Если значение по умолчанию для столбца отсутствует, то столбец получит значение NULL.

Допускается указывать несколько предложений WHEN MATCHED и WHEN NOT MATCHED.

Предложение WHEN NOT MATCHED BY TARGET вызывается, когда исходная запись не совпадает с ни с одной записью в целевой таблице. INSERT изменит целевую таблицу.

Предложение WHEN NOT MATCHED BY SOURCE вызывается, когда целевая запись не совпадает ни с одной записью в источнике. UPDATE или DELETE изменяют целевую таблицу.

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

Примеры использования MERGE.

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

Предположим, у нас есть две таблицы: EMPLOYEE (целевая таблица) и NEW_EMPLOYEES (источник данных). Мы хотим:

  • Обновить данные сотрудников, если они уже существуют в таблице EMPLOYEE.
  • Вставить новых сотрудников, если их нет в таблице EMPLOYEE.

Таблица EMPLOYEE:

EMP_NOFIRST_NAMELAST_NAMEDEPT_NOSALARYHIRE_DATE
1ИванИвановD01500002020-01-15
2МарияПетроваD02600002019-05-20

Таблица NEW_EMPLOYEES:

EMP_NOFIRST_NAMELAST_NAMEDEPT_NOSALARYHIRE_DATE
2МарияПетроваD02650002019-05-20
3АлексейСидоровD01550002021-03-10

Запрос MERGE:

MERGE INTO EMPLOYEE AS T
    USING NEW_EMPLOYEES AS S
    ON (T.EMP_NO = S.EMP_NO)
    WHEN MATCHED THEN
        UPDATE SET SALARY = S.SALARY, HIRE_DATE = S.HIRE_DATE
    WHEN NOT MATCHED THEN
        INSERT (EMP_NO, FIRST_NAME, LAST_NAME, DEPT_NO, SALARY, HIRE_DATE)
        VALUES (S.EMP_NO, S.FIRST_NAME, S.LAST_NAME, S.DEPT_NO, S.SALARY, S.HIRE_DATE)

Результат в таблице EMPLOYEE:

EMP_NOFIRST_NAMELAST_NAMEDEPT_NOSALARYHIRE_DATE
1ИванИвановD01500002020-01-15
2МарияПетроваD02650002019-05-20
3АлексейСидоровD01550002021-03-10

Удаление устаревших данных

Допустим, мы хотим удалить сотрудников, которые больше не работают в компании (их нет в таблице NEW_EMPLOYEES).

MERGE с удалением:

MERGE INTO EMPLOYEE AS T
    USING NEW_EMPLOYEES AS S
    ON (TARGET.EMP_NO = S.EMP_NO)
    WHEN MATCHED THEN
        UPDATE SET SALARY = S.SALARY, HIRE_DATE = S.HIRE_DATE
    WHEN NOT MATCHED BY SOURCE THEN
        DELETE

Результат в таблице EMPLOYEE:

EMP_NOFIRST_NAMELAST_NAMEDEPT_NOSALARYHIRE_DATE
2МарияПетроваD02650002019-05-20

Использование подзапроса

Вместо таблицы NEW_EMPLOYEES можно использовать подзапрос.

Запрос:

MERGE INTO EMPLOYEE AS T
    USING (SELECT * FROM NEW_EMPLOYEES WHERE DEPT_NO = 'D01') AS S
    ON (T.EMP_NO = S.EMP_NO)
    WHEN MATCHED THEN
        UPDATE SET SALARY = S.SALARY, HIRE_DATE = S.HIRE_DATE
    WHEN NOT MATCHED THEN
        INSERT (EMP_NO, FIRST_NAME, LAST_NAME, DEPT_NO, SALARY, HIRE_DATE)
        VALUES (S.EMP_NO, S.FIRST_NAME, S.LAST_NAME, S.DEPT_NO, S.SALARY, S.HIRE_DATE)

В результате обновляются или вставляются только сотрудники из отдела D01.

Дополнительные условия

В следующем примере происходит ежедневное обновление таблицы PRODUCTS на основе заказов, обработанных в таблице SALES. Если количество заказов на продукт таково, что уровень запасов продукта опускается до нуля или становится ещё ниже, то строка этого продукта удаляется из таблицы PRODUCTS.

MERGE INTO PRODUCTS AS T
    USING (SELECT ID_PRODUCT, SUM(QUANTITY) 
                FROM SALES WHERE BYDATE = CURRENT_DATE
                GROUP BY 1
    ) AS S(ID_PRODUCT, QUANTITY)
    ON T.ID_PRODUCT = S.ID_PRODUCT
    WHEN MATCHED AND T.QUANTITY - S.QUANTITY <= 0 THEN
        DELETE
    WHEN MATCHED THEN
        UPDATE SET T.QUANTITY = T.QUANTITY - S.QUANTITY, T.BYDATE = CURRENT_DATE

Контрольные вопросы

  1. Как вставить записи в несколько таблиц?
  2. Можно ли вставить запись, не указывая вставляемые столбцы?
  3. Какие значения получат столбцы, если им явно не указали значения?
  4. В каком случае может быть вставлено несколько записей?
  5. Можно ли вставить запись, вообще не указывая значений?
  6. Как получить сгенерированное сервером значение первичного ключа для только что вставленной записи?
  7. Какие записи будут обновлены если не указывать WHERE?
  8. Приведите пример обновления нескольких столбцов таблицы в одном операторе.
  9. Можно ли использовать столбцы, для которых выполняется обновление, для обновления других столбцов?
  10. Как выбрать новое значение столбца в зависимости от некоторого условия?
  11. Как получить новые значения столбцов?
  12. Каким образом проверяется существование записи в таблице, если в операторе UPDATE OR INSERT не указано предложение MATCHING?
  13. Сколько записей удалит оператор DELETE, если не указать условие поиск записей?
  14. Может ли оператор DELETE вернуть записи?
  15. Что произойдет, если на удаляемую запись существуют ссылки в других таблицах?
  16. Приведите примеры слияния наборов данных?
  17. Что произойдет с записями источника, которые не найдут совпадения в целевой таблице?
  18. Что произойдет с записями целевой таблицы, для которых не найдется совпадений в источнике?
  19. Что произойдет с совпавшими записями при слиянии?
  20. Для чего используются дополнительные условия в условиях совпадения?