SQL UPDATE

Change named columns on a reviewed population. A missing WHERE is a table-wide write, not a shortcut.

Preview UPDATE with the same WHERE

Before changing production rows, run a SELECT that uses the same predicate you plan to write. If the preview shows the wrong product, the update would have been wrong too. Treat “I meant that one row” as a claim you prove, not a feeling.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price_cents INTEGER NOT NULL
);

INSERT INTO products VALUES
  (1, 'SQL notebook', 1499),
  (2, 'Database sticker', 299),
  (3, 'Schema poster', 899);

SELECT product_id, product_name, price_cents
FROM products
WHERE product_id = 1;

Edit the query, predict the rows it will return, then run it.

UPDATE SET ... WHERE id = ...

SET names the new values. WHERE names the population. After the write, select the whole table so you can see that product 1 moved to 1299 cents and the others did not. Verification is cheaper than an incident.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price_cents INTEGER NOT NULL
);

INSERT INTO products VALUES
  (1, 'SQL notebook', 1499),
  (2, 'Database sticker', 299),
  (3, 'Schema poster', 899);

UPDATE products
SET price_cents = 1299
WHERE product_id = 1;

SELECT product_id, product_name, price_cents
FROM products
ORDER BY product_id;

Edit the query, predict the rows it will return, then run it.

A missing WHERE is a table-wide write

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price_cents INTEGER NOT NULL
);

INSERT INTO products VALUES
  (1, 'SQL notebook', 1499),
  (2, 'Database sticker', 299),
  (3, 'Schema poster', 899);

UPDATE products
SET price_cents = 100;

SELECT product_id, product_name, price_cents
FROM products
ORDER BY product_id;

Edit the query, predict the rows it will return, then run it.

Constraints still reject bad updates

Updates are not exempt from table rules. A negative price should fail CHECK (price_cents >= 0). Do not turn the rule off. Change the value, or change the schema if the rule itself is wrong. The rejected write leaves the previous valid row in place.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price_cents INTEGER NOT NULL CHECK (price_cents >= 0)
);

INSERT INTO products VALUES
  (1, 'SQL notebook', 1499);

SELECT product_id, product_name, price_cents
FROM products;

Edit the query, predict the rows it will return, then run it.

Independent lab: change one product price

Preview product 1, change only that price to 1299 cents, then read every row. The sticker and poster must keep 299 and 899. If your verification query hides the other products, you have not proved the blast radius.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price_cents INTEGER NOT NULL
);

INSERT INTO products VALUES
  (1, 'SQL notebook', 1499),
  (2, 'Database sticker', 299),
  (3, 'Schema poster', 899);

-- Preview the same population you will write:
SELECT product_id, product_name, price_cents
FROM products
WHERE product_id = 1;

UPDATE products
SET price_cents = 1299
WHERE product_id = 1;

SELECT product_id, product_name, price_cents
FROM products
ORDER BY product_id;

Edit the query, predict the rows it will return, then run it.

Common mistakes to avoid

• Updating without a preview SELECT that uses the same WHERE.
• Omitting WHERE and rewriting the whole table — a dangerous table-wide write, not a shortcut.
• Swallowing a constraint error and storing the bad value another way.
• Trusting the write succeeded without reading nearby rows that must not have changed.

Lesson review

UPDATE changes existing rows. Preview with the same WHERE, write SET on that population, then read the table including rows that should be untouched. A missing WHERE is a table-wide write; treat it as danger, not as the default. Constraints still reject illegal values.

• I can preview an UPDATE with the same WHERE.
• I can write UPDATE ... SET ... WHERE id = ... for one row.
• I can explain why a missing WHERE is a dangerous table-wide write.
• I can verify that neighboring rows did not change.