<link rel="stylesheet" href="https://fonts.googleapis.com/css2?family=Outfit:wght@600;700;800&family=Plus+Jakarta+Sans:wght@400;500;600;700&display=swap">
πŸ’» Developer guide

How to Write Safe SQL INSERT, UPDATE and DELETE Statements

Avoid UPDATE and DELETE disasters: preview with SELECT, use transactions, escape values, batch inserts, write upserts per dialect and prevent SQL injection.

πŸ“… Published 2026-11-15⏱️ 5 min readπŸ› οΈ 6 free tools

SELECT statements are forgiving: a wrong query returns wrong rows and you try again. Statements that change data are not. An UPDATE with a missing WHERE clause rewrites every row in the table, and a DELETE run against the wrong environment is only undone by restoring a backup, if there is one.

This guide covers the habits that make data-changing SQL safe to run by hand: previewing affected rows, wrapping changes in transactions, quoting values correctly, inserting in sensible batches, writing upserts for each major database, and knowing when generated SQL is fine and when you must use parameterised queries instead.

The WHERE clause is the whole statement

UPDATE users SET plan = ’free’; is valid SQL. So is DELETE FROM orders;. Neither asks for confirmation. The most common causes of accidental mass changes are a WHERE clause that was forgotten, one that was lost when only part of the query was selected in the editor before pressing run, and one that is wider than intended (status = ’trial’ when you meant a single user).

Some safeguards exist. MySQL has a safe-updates mode (SET SQL_SAFE_UPDATES = 1, or --safe-updates in the client) that rejects UPDATE and DELETE without a key in WHERE or a LIMIT. Many SQL clients warn before running an unfiltered change. Turn these on for any session that touches production, but do not rely on them as your only protection.

Preview with SELECT, then change inside a transaction

Before running an UPDATE or DELETE, run a SELECT COUNT(*) with exactly the same FROM and WHERE. If you expect to change three rows and the count says 48,000, stop. Then write the change inside an explicit transaction, check the affected row count your client reports, and only then commit.

Transactions protect you only when they are used correctly. Some clients run in autocommit mode, where each statement commits immediately unless you start a transaction yourself. DDL statements such as DROP and TRUNCATE commit implicitly in MySQL and Oracle and cannot be rolled back there, while PostgreSQL and SQL Server allow most DDL inside a transaction. Know which rules apply before you depend on ROLLBACK.

-- 1. Preview
SELECT COUNT(*) FROM users WHERE id IN (101, 102, 103);

-- 2. Change inside a transaction
BEGIN;                         -- START TRANSACTION in MySQL
UPDATE users
SET plan = 'pro'
WHERE id IN (101, 102, 103);
-- client reports: 3 rows affected

-- 3. Verify, then decide
SELECT id, plan FROM users WHERE id IN (101, 102, 103);
COMMIT;                        -- or ROLLBACK;

Quoting and escaping values

Most broken INSERT statements come from values, not syntax. Text values go in single quotes, and a single quote inside the value is doubled: ’O’’Brien’. Backslash escaping (\’) works in MySQL by default but is not standard SQL and fails in PostgreSQL with standard_conforming_strings on, which is the default.

Other values have their own rules. NULL is a keyword and must not be quoted; ’NULL’ is the four-letter string. Numbers are unquoted, except values where leading zeros matter, such as postal codes and phone numbers, which belong in text columns as quoted strings. Dates should use the unambiguous ISO format ’2026-11-15’ rather than 11/15/2026, which different servers read differently. Identifiers that are reserved words or contain spaces need identifier quotes: "order" in standard SQL, backticks in MySQL, [order] in SQL Server.

Multi-row INSERT and batch sizes

Inserting one row per statement is slow for anything beyond a few hundred rows, because each statement is a round trip and, in autocommit mode, a separate commit. A single INSERT with many VALUES tuples is much faster and is supported by PostgreSQL, MySQL, SQL Server and SQLite.

Do not put everything in one statement. Very large statements hit limits such as max_allowed_packet in MySQL, the 1,000-row limit on a VALUES list in SQL Server, and parameter limits in drivers. Batches of a few hundred to a few thousand rows, each in its own transaction, are a sensible default. For millions of rows, use the bulk loader built for the job: COPY in PostgreSQL, LOAD DATA in MySQL, BULK INSERT or bcp in SQL Server.

INSERT INTO products (sku, name, price)
VALUES
  ('A100', 'Desk lamp', 1299.00),
  ('A101', 'O''Neill chair', 4599.00),
  ('A102', 'Notebook', NULL);

Upserts differ in every database

An upsert inserts a row or updates it if a row with the same key already exists. It requires a unique constraint or primary key to detect the conflict, and the syntax depends on the database:

  • PostgreSQL and SQLite: INSERT ... ON CONFLICT (sku) DO UPDATE SET price = EXCLUDED.price;
  • MySQL and MariaDB: INSERT ... ON DUPLICATE KEY UPDATE price = VALUES(price); newer MySQL versions prefer a row alias (AS new ... price = new.price) because VALUES() in this position is deprecated.
  • SQL Server and Oracle: MERGE INTO target USING source ON (...) WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...; under concurrency, add appropriate locking hints or handle the duplicate-key error.
  • Avoid "SELECT, then INSERT if missing" in application code: two requests can both see nothing and both insert, which is exactly the race condition upserts exist to prevent.

Generated SQL versus parameterised queries

Building SQL by pasting values into a string is fine for one-off scripts, seed data and admin tasks that you review before running. It is never fine in application code that handles user input. String-built queries are the root cause of SQL injection, where input such as ’ OR ’1’=’1 changes the meaning of the statement.

In applications, always use parameterised queries or prepared statements, where the SQL text and the values are sent separately and the database never interprets a value as code. Every mainstream driver and ORM supports this. Escaping functions are a fallback, not an equivalent. Also use a database account with only the permissions the application needs, so a mistake cannot drop tables.

// Unsafe: value becomes part of the SQL text
db.query("DELETE FROM sessions WHERE user_id = " + req.params.id);

// Safe: value sent as a parameter (node-postgres syntax)
db.query("DELETE FROM sessions WHERE user_id = $1", [req.params.id]);

A safe workflow for hand-written changes

For seed data or a spreadsheet of rows, paste the data into the Neemtools SQL INSERT Generator, which quotes text, doubles apostrophes and turns empty cells into NULL for you. For single changes, the SQL UPDATE Generator and SQL DELETE Generator both refuse to produce an unfiltered statement: an empty WHERE becomes WHERE 1 = 0, which matches nothing. The SQL Query Generator covers quick SELECTs for the preview step.

Whatever writes the statement, the order stays the same: preview with SELECT, run the change in a transaction, check the affected row count, verify, then commit. For production, add a ticketed change record, a backup or point-in-time recovery check before large changes, and a second person to review the statement.

More guides you might like

Ready to try it?

Open the free All SQL Tools and apply what you just read. No sign-up, nothing uploaded.

Open All SQL Tools β†’

← All guides