INSERT, UPDATE, DELETE

DML (Data Manipulation Language) operations are used to add, update, and delete data in TiDB.

INSERT

Basic Syntax

-- Insert a single row
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

-- Insert multiple rows
INSERT INTO table_name (column1, column2)
VALUES (value1, value2), (value3, value4), (value5, value6);

Example

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_RANDOM,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert a single row
INSERT INTO users (name, email) VALUES ('John', '[email protected]');

-- Insert multiple rows
INSERT INTO users (name, email) VALUES
('Mike', '[email protected]'),
('Anna', '[email protected]'),
('Dana', '[email protected]');

INSERT ... ON DUPLICATE KEY UPDATE

If a duplicate key occurs, update the existing row:

INSERT INTO users (name, email)
VALUES ('John', '[email protected]')
ON DUPLICATE KEY UPDATE name = VALUES(name);

UPDATE

Basic Syntax

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

Example

-- Update a single column
UPDATE users SET name = 'John Smith' WHERE id = 1;

-- Update multiple columns
UPDATE users
SET name = 'Mike Johnson', email = '[email protected]'
WHERE id = 2;

-- Update with an expression
UPDATE products SET stock = stock - 10 WHERE category_id = 5;

Warning: An UPDATE without a WHERE clause affects EVERY row in the table!

DELETE

Basic Syntax

DELETE FROM table_name WHERE condition;

Example

-- Delete a single row
DELETE FROM users WHERE id = 3;

-- Delete multiple rows
DELETE FROM users WHERE status = 'inactive';

-- Delete all data (faster with TRUNCATE)
DELETE FROM users;

Warning: A DELETE without a WHERE clause removes ALL data in the table!

REPLACE

REPLACE works like INSERT, but if a duplicate key occurs, the old row is deleted and replaced with the new one:

REPLACE INTO users (id, name, email)
VALUES (1, 'John Updated', '[email protected]');

Batch DML (TiDB Feature)

TiDB supports batch DML for large-scale data operations:

-- Batch DELETE
BATCH ON user_id LIMIT 1000 DELETE FROM users WHERE status = 'inactive';

-- Batch UPDATE
BATCH ON user_id LIMIT 1000
UPDATE users SET status = 'archived' WHERE created_at < '2023-01-01';

Tips

• Always use a WHERE clause with UPDATE and DELETE
• Use transactions for critical operations
• Use BATCH for large-scale data operations
• AUTO_RANDOM helps reduce hotspots on INSERT

In the next chapter, we will look at SELECT queries in TiDB.