Вычисления в UPDATE

Новое значение в SET не обязано быть постоянным. Его можно вычислить, в том числе из значений самой строки. Когда магазин поднимает цену всех спортивных игр на $5, ему не нужно знать их цены:

UPDATE games
SET price = price + 5
WHERE genre = 'Sports';

Для каждой строки, которую находит WHERE, SQLite берёт её текущую цену, прибавляет 5 и сохраняет результат как новую цену: Goal Rush дорожает с $50 до $55, Ice Hockey Pro — с $45 до $50. Можно использовать любое вычисление и любую известную вам функцию, а при изменении нескольких столбцов у каждого может быть своё вычисление: SET price = price - 10, age_rating = 12. Все вычисления используют значения, которые были у строки до UPDATE.

Теперь скидка 10% на экшен-игры. Их новая цена — 90% от старой, поэтому её умножают на 0.9:

UPDATE games
SET price = price * 0.9
WHERE genre = 'Action';

Error on line 1: cannot store REAL value in INTEGER column games.price

Таблицы Pixel Shop — STRICT: каждый столбец хранит только значения своего типа, а price — это INTEGER, целое число. Shadow Ninja стоит $25, и 90% от этого — 22.5, десятичное число (REAL), которое столбец вместить не может. Поэтому база данных отклоняет всю инструкцию, и ни одна игра не получает новую цену, даже Zombie Town, чья новая цена была бы целыми $27. Инструкция либо меняет все строки, которые должна, либо не меняет ни одной.

Исправляет это ROUND(), который вы знаете из урока о функциях. ROUND(price * 0.9) даёт для Shadow Ninja 23.0. Это по-прежнему десятичное число, но без дробной части, а такое число помещается в столбец INTEGER, где хранится как 23:

UPDATE games
SET price = ROUND(price * 0.9)
WHERE genre = 'Action';

SELECT title, price
FROM games
WHERE genre = 'Action';

Без STRICT SQLite всё равно сохранил бы в столбце 22.5, а другие базы данных, например PostgreSQL или MySQL, молча округлили бы его. Ошибка лучше: вы сами решаете, как округлять. Будьте внимательны и с делением: price / 2 делит два целых числа, поэтому, как вы знаете, даёт целое число и отбрасывает остаток без всякой ошибки (15 / 2 — это 7).

Иногда разным строкам нужны разные изменения. Один UPDATE может внести их с помощью CASE, который даёт каждой строке своё новое значение. Здесь головоломки дорожают на $2, а гонки — на $5:

UPDATE games
SET price = CASE
        WHEN genre = 'Puzzle' THEN price + 2
        ELSE price + 5
    END
WHERE genre IN ('Puzzle', 'Racing');

Типичная ошибка: CASE без ELSE. Для строк, не подходящих ни под один WHEN, CASE даёт NULL, и у гоночных игр пропали бы цены. Здесь база данных откажет с NOT NULL constraint failed: games.price, но в столбце, который допускает NULL, значения тихо исчезли бы. Если строка должна сохранить значение, пишите ELSE price.