UPDATE without WHERE changed every row
It is one of the questions on the editor page, and it usually arrives in capitals. You meant to change one row, you typed the statement into the console, and the message underneath said every row had changed. Nothing is broken. The console did exactly what the statement asked. Here is one slip worked through with real numbers, the two ways back, and the habit that makes it rare.
The test table
A small membership list: 250 rows, with an id, a name, a city and a status. It was written to a .sqlite file by sqlwrite.js, the same writer the editor uses for exports, then read back with sqlite.js and edited with sqlmini.js, the console's own engine. All three ran under Node so every count below could be captured. At the start, 223 members were active and 27 were lapsed.
The job: mark member 118, Kiran U. from Bristol, as lapsed.
The slip
This is what went into the console:
UPDATE members SET status = 'lapsed'
The reply was UPDATE: 250 rows in members — undo has your back. A count of the status column afterwards: 250 lapsed, 0 active. The 223 active members were gone in one keystroke.
The reason is simple once seen. In SQL, WHERE is a filter. An UPDATE visits every row of the table and changes the ones the filter lets through. Leave the filter out and nothing is filtered, so every row goes through. The official sqlite3 tool, MySQL and Postgres all behave the same way; it is the language, not a quirk of this editor. In the console's engine it is a single line: when there is no WHERE, the test applied to each row simply returns true. DELETE FROM members with no WHERE works the same way and empties the table.
Way back one: undo
Before every UPDATE, DELETE or INSERT, the console takes a snapshot of the table, so one press of undo puts all 250 rows back as they were. It keeps the last 40 snapshots, and redo works too. Two limits are worth knowing before you need them.
The snapshot is of the table you have open. The console can change any table by name, but the checkpoint records the one showing in the editor. If the grid is showing orders and you run UPDATE members ..., undo restores orders, which did not change, and members stays as the statement left it. Open the table you are about to change before you run the statement against it.
Dropping a table clears undo. Snapshots that span a table which no longer exists would restore the wrong thing, so the history is reset.
Way back two: the original file
The editor never writes to the file you opened. Every edit happens on a working copy in the tab, and an export is always a new file. So when undo cannot help, open the original again. In the test, re-reading the untouched file gave 223 active and 27 lapsed, exactly the starting state. You lose any edits made since you opened it, which is a good reason to export after each piece of work you would not want to redo.
The habit: SELECT first
Write the WHERE as a SELECT before you write it as an UPDATE:
SELECT id, name, status FROM members WHERE id = 118
That returned one row: 118 | Kiran U. | active. One row is what the job needs, so the same filter goes on the end of the change:
UPDATE members SET status = 'lapsed' WHERE id = 118
The reply was UPDATE: 1 row in members — undo has your back, and the counts became 222 active and 28 lapsed. The edited table was exported with the writer and handed to the official engine: PRAGMA integrity_check; returned ok, and grouping by status returned the same 222 and 28.
The preview matters most when the filter is not an id. WHERE city = 'Leeds' AND status = 'active' sounds like a handful of people; on this table it matched 45. Better to learn that from a SELECT than from the reply to an UPDATE.
Read the count in the reply
The row count after every change is the second check. If you expected 1 and it says 250, press undo straight away, before anything else touches the table.
One thing the count does not tell you: whether a value actually changed. While preparing this test, member 117 was tried first. The console replied UPDATE: 1 row, yet the totals did not move, because 117 was already lapsed. The count is the number of rows the filter matched, which is also how sqlite3 counts. When it matters, look at the row afterwards.
The short version: no WHERE means every row, in every SQL engine. Here it costs one press of undo, or a reload of a file that was never touched. Keep the original either way, as the editor page says, and if the console ever changes a different number of rows than its SELECT preview showed, write to the address on the contact page with both statements.