UPDATE table SET column = value WHERE condition; changes rows. DELETE FROM table WHERE condition; removes them.
Without a WHERE, both hit every row in the table. Habit: write the WHERE as a SELECT first, check which rows come back, then turn it into the UPDATE or DELETE.
RETURNING columns on the end of an INSERT, UPDATE or DELETE hands back the affected rows, so you see what you changed without a second query.
A transaction groups statements so they all succeed or none do. BEGIN; starts one. COMMIT; makes the changes permanent. ROLLBACK; throws them away. Until you commit, nobody else sees your changes.
New values can use old ones: SET view_count = view_count + 1.
A soft delete marks a row as deleted instead of removing it, usually with a nullable deleted_at timestamp. NULL means live. Every query that shows the data then has to filter on it.
Adding a NOT NULL column to a table that already has rows needs a DEFAULT, so the existing rows have something to hold.