How do I verify that a data fix changed only the intended rows?

Applies to BroadSQL 5.5.0 · reviewed October 18, 2026

What you'll accomplish

A ticket asks for a correction in production: move the cities of Romania and East Timor from their old country codes to the current ones. The change control wants more than the row count the database prints after an UPDATE: proof that the statement changed exactly the rows the ticket named, only in the column it named, and nothing else.

You will copy the whole table before the fix and again after it into a local H2 database, compare the two copies, and read the result: 30 rows changed, all of them in COUNTRYCODE, all of them the expected IDs. You will rehearse on QA first, then apply the fix on production through BroadSQL's Production confirmation, which shows you the exact statement before anything is sent.

Short answer

DUMP the table AS H2 into a local database before the fix, apply it, DUMP it again into another table, then compare the two copies with SQL joins on the key. On production, read the statement in the confirmation before you answer yes.

Before you start

This page needs the practice connections of Set up QA and production copies of WORLD, which do not exist by default: WORLD-QA, and WORLD as the production database of WORLDAPP, flagged Production. The Production confirmation is the subject of steps 6 to 8.

This page changes data in WORLD-QA and in WORLD. The rows it moves are the orphan cities that Find orphan rows, duplicates and missing values and Investigate a support ticket find, and Clean up puts them back exactly, so that those pages still work.

Write down the expected effect before touching anything. It is what the comparison will be checked against:

1. Rehearse on QA: snapshot before

Open the QA copy and copy the whole CITY table into the local database FIXCHECK. The whole table, not only the rows you intend to change: the comparison must also prove what did not change. TRIM removes the padding of WORLD's fixed-length text columns:

CONNECT WORLD-QA;
DUMP (SELECT ID, TRIM(NAME) AS NAME, COUNTRYCODE, TRIM(DISTRICT) AS DISTRICT, POPULATION FROM CITY) TO FIXCHECK.QA_BEFORE AS H2;

The first DUMP creates FIXCHECK as a local H2 database in the export folder, registers it as a connection, and copies the 4081 cities.

2. Apply the fix to the QA copy

These two statements change WORLD-QA, your practice copy:

UPDATE CITY SET COUNTRYCODE = 'ROU' WHERE COUNTRYCODE = 'ROM';
UPDATE CITY SET COUNTRYCODE = 'TLS' WHERE COUNTRYCODE = 'TMP';

The database reports 29 rows, then 1 row. Those counts say how many rows each statement touched, not which ones.

3. Snapshot after

Copy the table again, with the same query, into a second table:

DUMP (SELECT ID, TRIM(NAME) AS NAME, COUNTRYCODE, TRIM(DISTRICT) AS DISTRICT, POPULATION FROM CITY) TO FIXCHECK.QA_AFTER AS H2;

4. Compare the two copies

Open the local database, where both copies now are:

CONNECT FIXCHECK;

First, no row may have appeared or disappeared. These two queries list the IDs found in one copy only; both must return no row:

SELECT B.ID FROM QA_BEFORE B LEFT JOIN QA_AFTER A ON A.ID = B.ID WHERE A.ID IS NULL;
SELECT A.ID FROM QA_AFTER A LEFT JOIN QA_BEFORE B ON B.ID = A.ID WHERE B.ID IS NULL;

Then the rows present on both sides whose values differ, in any column, grouped by the change they underwent:

SELECT B.COUNTRYCODE AS BEFORE_CODE, A.COUNTRYCODE AS AFTER_CODE, COUNT(*) AS CITIES, MIN(B.ID) AS FIRST_ID, MAX(B.ID) AS LAST_ID
  FROM QA_BEFORE B JOIN QA_AFTER A ON A.ID = B.ID
 WHERE B.NAME IS DISTINCT FROM A.NAME OR B.COUNTRYCODE IS DISTINCT FROM A.COUNTRYCODE
    OR B.DISTRICT IS DISTINCT FROM A.DISTRICT OR B.POPULATION IS DISTINCT FROM A.POPULATION
 GROUP BY B.COUNTRYCODE, A.COUNTRYCODE ORDER BY BEFORE_CODE;

The result is exactly the table of Before you start:

FIXCHECK> SELECT B.COUNTRYCODE AS BEFORE_CODE, A.COUNTRYCODE AS AFTER_CODE, COUNT(*) AS CITIES, MIN(B.ID) AS FIRST_ID, MAX(B.ID) AS LAST_ID FROM QA_BEFORE B JOIN QA_AFTER A ON A.ID = B.ID WHERE B.NAME IS DISTINCT FROM A.NAME OR B.COUNTRYCODE IS DISTINCT FROM A.COUNTRYCODE OR B.DISTRICT IS DISTINCT FROM A.DISTRICT OR B.POPULATION IS DISTINCT FROM A.POPULATION GROUP BY B.COUNTRYCODE, A.COUNTRYCODE ORDER BY BEFORE_CODE;
|-----------|----------|--------------------|-----------|-----------|
|BEFORE_CODE|AFTER_CODE|CITIES              |FIRST_ID   |LAST_ID    |
|-----------|----------|--------------------|-----------|-----------|
|ROM        |ROU       |29                  |3018       |3046       |
|TMP        |TLS       |1                   |1522       |1522       |
|-----------|----------|--------------------|-----------|-----------|

2 rows fetched in 9 ms.

The query compares every column, so a row whose name, district or population had changed would appear here too, with its codes unchanged. To prove it directly, compare every column except COUNTRYCODE; this must return no row:

SELECT B.ID FROM QA_BEFORE B JOIN QA_AFTER A ON A.ID = B.ID
 WHERE B.NAME IS DISTINCT FROM A.NAME OR B.DISTRICT IS DISTINCT FROM A.DISTRICT OR B.POPULATION IS DISTINCT FROM A.POPULATION;

IS DISTINCT FROM treats two NULL values as equal and a NULL against a value as a difference, which <> does not.

5. What the comparison would have caught

Had the first UPDATE been typed without its WHERE, the database would have reported 4081 rows, and the grouped comparison would have shown 4081 cities moved to ROU from every code of the table. Had it been typed with WHERE COUNTRYCODE LIKE 'RO%', any other code starting with RO would have appeared as a line of its own. The rehearsal is where such a mistake costs nothing.

6. Production: snapshot before

The rehearsal matched the expectation, so apply the fix to production. ENV PROD opens the production connection of the Database Group; when colors are on, the prompt takes the Production style. Copy the table first: a DUMP that reads from production never asks for a confirmation, since it changes nothing there:

ENV PROD;
DUMP (SELECT ID, TRIM(NAME) AS NAME, COUNTRYCODE, TRIM(DISTRICT) AS DISTRICT, POPULATION FROM CITY) TO FIXCHECK.PROD_BEFORE AS H2;

7. Apply the fix through the Production confirmation

Type the first statement. Because WORLD belongs to an Environment flagged Production, BroadSQL shows the connection, the Environment, the whole statement and why it asks, and sends nothing until you answer:

UPDATE CITY SET COUNTRYCODE = 'ROU' WHERE COUNTRYCODE = 'ROM';

The question looks like this:

WORLD> UPDATE CITY SET COUNTRYCODE = 'ROU' WHERE COUNTRYCODE = 'ROM';
WARNING: This operation can modify a Production database.
  Connection: WORLD (Environment PROD, Production)
  Statement:  UPDATE CITY SET COUNTRYCODE = 'ROU' WHERE COUNTRYCODE = 'ROM'
  Why:        it is an UPDATE
Execute? [yes/no]: yes
29 row(s) updated.

This is the last moment to read the WHERE. Answer yes only if the statement is the one you rehearsed; any other answer refuses it and nothing is executed. Then the second statement, with the same question:

UPDATE CITY SET COUNTRYCODE = 'TLS' WHERE COUNTRYCODE = 'TMP';

8. Production: snapshot after and compare

Copy the table again, then run the comparisons of step 4 on the production copies:

DUMP (SELECT ID, TRIM(NAME) AS NAME, COUNTRYCODE, TRIM(DISTRICT) AS DISTRICT, POPULATION FROM CITY) TO FIXCHECK.PROD_AFTER AS H2;
CONNECT FIXCHECK;

In the queries of step 4, write PROD_BEFORE and PROD_AFTER instead of QA_BEFORE and QA_AFTER: the result is the same two lines, 29 and 1.

9. QA and production received the same change

Last, compare the two results of the fix with each other. The keys found on one side only, and the rows whose values differ, must all be empty:

SELECT P.ID FROM PROD_AFTER P LEFT JOIN QA_AFTER Q ON Q.ID = P.ID WHERE Q.ID IS NULL;
SELECT Q.ID FROM QA_AFTER Q LEFT JOIN PROD_AFTER P ON P.ID = Q.ID WHERE P.ID IS NULL;
SELECT P.ID FROM PROD_AFTER P JOIN QA_AFTER Q ON Q.ID = P.ID
 WHERE P.NAME IS DISTINCT FROM Q.NAME OR P.COUNTRYCODE IS DISTINCT FROM Q.COUNTRYCODE
    OR P.DISTRICT IS DISTINCT FROM Q.DISTRICT OR P.POPULATION IS DISTINCT FROM Q.POPULATION;

These are the three queries of Find the rows that differ between two databases.

10. Put the record together

For the ticket and the change control:

• The changed rows: after the grouped comparison of step 8, DUMP / TO fix_4711_changed_rows AS MD; writes it as a Markdown table to attach.
• Where the copies came from: every AS H2 export adds a row to BROADSQL_PULL_AUDIT inside FIXCHECK, with the source query, the connection, the time and the row count: SELECT TARGET_TABLE, EXECUTED_AT FROM BROADSQL_PULL_AUDIT ORDER BY EXECUTED_AT;.
• Your own record of the session: with the activity log on, the log of WORLD holds the statements and the answer to each confirmation ("Production confirmation for WORLD: approved"). It is a convenience for your notes, not an audit trail: see Command activity log.

Variations

• A large table: copy a subset wide enough to include the rows that must not change, for example every city of the countries whose code starts with R, rather than the whole table.
• The languages too: COUNTRYLANGUAGE holds 6 rows under ROM and 2 under TMP. The same method applies, with the key (COUNTRYCODE, LANGUAGE).
• A fix delivered as a Script: run it with @ on QA between the two snapshots; on production, the Script asks at each change and a refusal stops the whole run.

Watch out

• Take the snapshots with the same query before and after, or the comparison compares different things.
• ProductionConfirmEveryChange=FALSE asks once per session instead of at every change. For a fix, keep the default and read each statement.
• Each DUMP ... AS H2 replaces its target table by default (MODE OVERWRITE), so a new rehearsal starts from fresh copies.
• Snapshots of a large table take time and disk space in the export folder.

Clean up

Put the cities back under their original codes, so that the other WORLD pages keep working. On WORLD-QA:

CONNECT WORLD-QA;
UPDATE CITY SET COUNTRYCODE = 'ROM' WHERE ID BETWEEN 3018 AND 3046;
UPDATE CITY SET COUNTRYCODE = 'TMP' WHERE ID = 1522;

Instead, you can replace samples/WorldDB_QA.mv.db with a new copy of the sample while BroadSQL is closed. WORLD is the original sample and cannot be copied back from itself, so restore it with the same two statements, after ENV PROD; (or CONNECT WORLD;), answering yes to each confirmation. Then remove the local database: DEL CONNECTION FIXCHECK; makes the connection inactive, and you delete FIXCHECK.mv.db from the export folder while BroadSQL is closed. Delete fix_4711_changed_rows.md too.

Related

• Production confirmation
• Local H2 datasets
• Find the rows that differ between two databases
• Investigate a support ticket and keep the evidence

Commands used

• DUMP
• CONNECT
• ENV
• DEL CONNECTION

Found something wrong or unclear on this page? Tell us, with the page address.