GeezerVison is showing github.com — Simulate query for UPDATE/DELETE – preview affected rows per SELECT · Issue #2646 · HeidiSQL/HeidiSQL
Simulate query for UPDATE/DELETE – preview affected rows per SELECT #2646DescriptionIs your feature request related to a problem? Please describe.
Before running an UPDATE or DELETE on a production database I want to see which rows it will touch, without writing anything. phpMyAdmin has a "Simulate query" button for exactly this. In HeidiSQL I have to rewrite the statement into a SELECT by hand, or wrap it in START TRANSACTION / ROLLBACK, every single time. Both are error-prone and slow for one-off maintenance queries.
Describe the solution you'd like
A new action "Simulate current query" next to "Explain current query" (Query menu, toolbar, shortcut). It rewrites the UPDATE/DELETE under the cursor into a read-only SELECT with the same FROM/JOIN and WHERE clause, runs it, and shows the result in a normal result tab with the row count in the status bar.
- UPDATE: show the primary key, the current value of each SET column and the new value as an expression
- DELETE: show the rows that would be removed
- INSERT and DDL: out of scope
Example – input:
UPDATE t SET col = REPLACE(col, 'a', 'b') WHERE id > 100;
simulated as:
SELECT id, col, REPLACE(col, 'a', 'b') AS col_new FROM t WHERE id > 100;
Nothing is written, autocommit stays on, no transaction handling needed.
Describe alternatives you've considered
- Manual rewrite to SELECT: works, but has to be done for every query.
- START TRANSACTION ... ROLLBACK: works for DML, but is easy to forget half-way and does not cover implicit commits.
- Keeping phpMyAdmin installed just for this one button.
Additional context
HeidiSQL 12.22.0.7382, MySQL 8.0.42. Checked the latest nightly, the feature does not exist there. Searched issues for "simulate", "dry run", "preview" – no existing request. Activityq2apro commented on Oct 30, 2026 A real example from today, to make the use case concrete. Column migration in six statements, flag bits moved into new columns and then cleared:
UPDATE edu_boxes SET ratingmode = CASE WHEN flags & 256 THEN 2 WHEN flags & 512 THEN 3 WHEN flags & 2 THEN 1 ELSE 0 END WHERE flags & 770;
UPDATE edu_boxhistory SET ratingmode = CASE WHEN flags & 256 THEN 2 WHEN flags & 512 THEN 3 WHEN flags & 2 THEN 1 ELSE 0 END WHERE flags & 770;
UPDATE edu_paths SET widthmode = CASE WHEN flags & 64 THEN 1 WHEN flags & 128 THEN 2 ELSE 0 END WHERE flags & 192;
UPDATE edu_boxes SET flags = flags & ~770 WHERE flags & 770;
UPDATE edu_boxhistory SET flags = flags & ~770 WHERE flags & 770;
UPDATE edu_paths SET flags = flags & ~192 WHERE flags & 192;
What I need / want before pressing CTRL+ENTER (or F9) is one result per statement:
# | Statement | Matched rows
1 | UPDATE edu_boxes SET ratingmode ... | 12 345
2 | UPDATE edu_boxhistory SET ratingmode ...| 98 765
...
i.e. "Simulate" applied to the whole tab (or selection), each UPDATE/DELETE rewritten to SELECT COUNT(*) FROM <table> WHERE <same where> and run read-only, like phpMyAdmin does for a single statement.
Then the chance to have a wrong query deleting / changing your production server data is way lower. ⚠️ I once queried without simulate in phpmyadmin and forgot one char, which was overriding the entire data of all entries of one column of my table. Took me hours to get back the original data.
Since then I always simulate first — so for me this is more than a nice-to-have.
Today I write a hand-made SELECT ... UNION ALL ... with the three WHERE clauses copied over, which is longer than the migration itself and easy to get out of sync with the real statements.
Known limitation, fine with me: each simulated SELECT runs against the current data, so statements that depend on an earlier one's effect (here 4–6 on 1–3) show pre-migration counts. That's still exactly the number I need to sanity-check the WHERE. ansgarbecker commented on Oct 30, 2026 There is already "Explain current query", which makes a deeper analysis and shows you index usage and more:
On a side note: HeidiSQL is not imitating phpMyAdmin, it has its own usage concept. q2apro commented on Oct 30, 2026 Thanks for the reply. I think the request got mixed up with EXPLAIN, so let me be more precise.
EXPLAIN answers "how will the server execute this" – index usage, estimated rows, join order. That's a performance tool.
What I'm asking for answers "which rows will this change, and what will they look like afterwards" – a correctness tool. EXPLAIN cannot show that: the row estimate is an estimate, and it never shows the actual key values or the before/after column values.
Concrete example. Given
UPDATE edu_eventlog SET params = REPLACE(params, '|p=x', '') WHERE event = 'box-aichange' AND params LIKE '%|p=x';
EXPLAIN tells me "range scan on idx_event, ~1200 rows". What I need to see before I press F9 on a production database is
id | params (current) | params (new)
4711 | ...|p=foo|p=x | ...|p=foo
4712 | ...|p=x |
... | (exact count: 1187 rows)
i.e. a SELECT of the primary key, each SET column and its new expression, with the same FROM and WHERE. Nothing written, autocommit untouched.
Forget the phpMyAdmin comparison – that was only meant as "this concept exists and works", not as "please copy it". The feature stands on its own: HeidiSQL already warns about UPDATE/DELETE without WHERE; this is the natural next step for the ones with a WHERE, where the danger is a wrong condition rather than a missing one.
Would you be open to it as "Simulate current query" alongside "Explain current query", or as a PR? changed the title [-]Simulate query for UPDATE/DELETE – preview affected rows like phpMyAdmin[/-] [+]Simulate query for UPDATE/DELETE – preview affected rows per SELECT[/+] on Oct 30, 2026ansgarbecker commented on Oct 30, 2026 No PR please for features. I will get into that myself. But yes, I'm open to that, thinking this could indeed help a few other users as well.
Thank you for understanding. You wouldn't believe how many requests come in which tell about a feature in some other tool, and declare that tool as defacto standard, and HeidiSQL has to follow that concept. Glad this is not the case here.
MetadataMetadataDevelopmentNo branches or pull requestsIssue actions
|
