Calculations in UPDATE

The new value in SET doesn't have to be fixed. It can be calculated, even from the row's own values. When the shop raises the price of every sports game by $5, it doesn't need to know their prices:

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

For each row that WHERE finds, SQLite takes its current price, adds 5 and stores the result as the new price: Goal Rush goes from $50 to $55, Ice Hockey Pro from $45 to $50. You can use any calculation and any function you know, and when you change several columns, each of them can have its own: SET price = price - 10, age_rating = 12. All the calculations use the values that the row had before the UPDATE.

Now a 10% sale on the action games. Their new price is 90% of the old one, so you multiply it by 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

The tables of Pixel Shop are STRICT: each column holds only values of its type, and price is an INTEGER, a whole number. Shadow Ninja costs $25, and 90% of that is 22.5, a decimal number (REAL), which the column can't hold. So the database refuses the whole statement, and no game gets a new price, not even Zombie Town, whose new price would be a whole $27. A statement either changes every row it should, or none.

The fix is ROUND(), which you know from the lesson on functions. ROUND(price * 0.9) gives 23.0 for Shadow Ninja. That's still a decimal number, but one with nothing after the point, and such a number fits in an INTEGER column, where it's stored as 23:

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

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

Without STRICT, SQLite would store 22.5 in the column anyway, and other databases, such as PostgreSQL or MySQL, would quietly round it. The error is better: you decide how to round. Watch out for division too: price / 2 divides two whole numbers, so, as you know, it gives a whole number and drops the rest, without any error (15 / 2 is 7).

Sometimes different rows need different changes. One UPDATE can make them with CASE, which gives each row its own new value. Here, the puzzle games get $2 more expensive and the racing games $5:

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

Typical mistake: a CASE without ELSE. For the rows that match no WHEN, CASE gives NULL, so the racing games would lose their prices. Here, the database refuses that with NOT NULL constraint failed: games.price, but in a column that allows NULL, the values would quietly disappear. When a row should keep its value, write ELSE price.