TSV to SQL UPDATE / UPSERT
You have a spreadsheet of corrections and a table that needs them applied. Plain INSERT generation doesn't help — those rows already exist. This writes one UPDATE … WHERE key = … per row, or an upsert if you're not sure the row exists yet, in the dialect you're actually running. Column types are inferred from the data so numbers stay unquoted and text is escaped properly.
How to use
- Paste or drop the file. The header names become the column names, so they must match the table.
- Name the key column — the one that identifies an existing row. Comma-separate for a composite key:
tenant_id,email. - Pick the statement. UPDATE touches only rows that exist; UPSERT inserts the ones that don't; DELETE removes by key and ignores every other column.
- Optionally restrict the SET list to specific columns. By default every non-key column is written.
- Read the SQL before running it. Then run it in a transaction against a copy first — the wrapper is on by default so a mistake rolls back.
What each dialect gets
PostgreSQL and SQLite get INSERT INTO … ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col. That requires a unique index or primary key on the conflict columns — without one, Postgres raises there is no unique or exclusion constraint matching the ON CONFLICT specification, which is a schema problem, not a generation problem.
MySQL and MariaDB get INSERT … ON DUPLICATE KEY UPDATE col = VALUES(col), which keys off any unique index on the table rather than the columns you named. If the table has a second unique index, a row can collide on that instead and update something you didn't intend.
SQL Server has no portable upsert, so it gets the honest two-statement form: UPDATE …; followed by IF @@ROWCOUNT = 0 INSERT …;. That is correct under a transaction and is what most hand-written SQL Server upserts do; MERGE is deliberately avoided given its long history of concurrency edge cases.
How values are typed and escaped
Each column is scanned across the whole file and typed as integer, decimal, boolean, date or text. Values in a numeric column are emitted bare; everything else is single-quoted with internal quotes doubled. Identifiers are quoted per dialect — double quotes for Postgres and SQLite, backticks for MySQL, square brackets for SQL Server — so a column called order or from works without special handling.
Two limits worth knowing. Empty cells become empty strings, not NULL, in the SET list — a TSV cannot distinguish the two, and guessing would silently null out columns. If you need real NULLs, generate the SQL and then find-and-replace = '' with = NULL for the columns where that's correct. In the WHERE clause an empty key does become IS NULL, since = '' would never match a NULL key. One statement per row is easy to read and slow to run: for tens of thousands of rows, load the TSV into a staging table with COPY and do a single set-based UPDATE … FROM instead.
FAQ
Can I update rows without a primary key?
You can name any column as the key — the WHERE clause is built from whatever you give it. Just be aware that if the key isn't unique, one statement updates every matching row. Check with value counts first: any value with a count above 1 is a multi-row update.
Why is the key column also in the INSERT list for an upsert?
Because the insert half needs it — a new row has to carry its own key. It's excluded from the update half, where re-setting the key to itself would be pointless and, in the ON CONFLICT form, illegal in some versions.
Are dates converted?
No. A column of YYYY-MM-DD values is typed as DATE and the values are quoted as-is, which every one of these databases accepts. Any other date format is passed through as text and will be parsed — or rejected — by the server according to its own settings. Normalise first with reformat dates if the source is DD/MM/YYYY.
Is the output safe against SQL injection?
Quotes in values are doubled, which is correct escaping for a string literal in all four dialects. But this generates a script for you to review and run yourself — it is not a substitute for parameterised queries in application code, and you should read what it produced before executing it against anything you care about.
Privacy
100% client-side. No upload. See the privacy policy.