Changing and removing rows

UPDATE changes values in rows that already exist:

UPDATE products
SET list_price = ROUND(list_price * 1.05, 2)
WHERE category = 'Catering'

That's a 5% price rise on catering products, rounded to the penny. SET can change several columns at once: SET a = 1, b = 2.

DELETE removes rows:

DELETE FROM order_items
WHERE quantity = 0

The WHERE is everything. UPDATE products SET list_price = 0 with no WHERE changes every product. Before you run an UPDATE or DELETE on real data, run the same WHERE as a SELECT first and check it picks the rows you expect.

Both can use a subquery to decide which rows to change:

DELETE FROM payments
WHERE id IN (
  SELECT id FROM (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY invoice_id, payment_date, amount, method ORDER BY id) AS rn
    FROM payments
  ) AS numbered
  WHERE rn > 1
)

That deletes every copy of a duplicated payment, keeping the first.