Cara Insert, Update, dan Delete Data di MySQL: DML Operations Lengkap

Update: 3 February 2026

Baca: 4 menit

Cara Insert, Update, dan Delete Data di MySQL: DML Operations Lengkap

Operasi DML (Data Manipulation Language) adalah inti dari daily database operations. Setelah mengoptimasi DML operations untuk high-traffic applications, saya akan berbagi teknik-teknik yang efisien dan safe.

INSERT Operations

1. Single Row Insert

-- Insert satu row
INSERT INTO users (username, email, created_at)
VALUES ('john_doe', 'john@example.com', NOW());

-- Insert dengan auto-increment
INSERT INTO products (name, price, category)
VALUES ('Laptop Dell', 999.99, 'electronics');

-- Dapatkan ID yang baru di-insert
SELECT LAST_INSERT_ID();

2. Multiple Row Insert

-- Insert multiple rows (lebih efisien!)
INSERT INTO users (username, email)
VALUES 
    ('alice', 'alice@example.com'),
    ('bob', 'bob@example.com'),
    ('charlie', 'charlie@example.com');

-- Biasanya bisa handle hingga 1000 rows per statement

3. INSERT dengan SELECT

-- Copy data dari table lain
INSERT INTO users_archive (username, email, created_at)
SELECT username, email, created_at
FROM users
WHERE status = 'inactive';

-- Insert dengan transformasi
INSERT INTO order_summary (order_month, total_sales)
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') as order_month,
    SUM(total_amount) as total_sales
FROM orders
GROUP BY order_month;

4. INSERT IGNORE

-- Insert tapi ignore duplicate key errors
INSERT IGNORE INTO users (username, email)
VALUES ('john_doe', 'john2@example.com');

-- Row dengan duplicate key akan di-skip tanpa error

5. INSERT ON DUPLICATE KEY UPDATE

-- Update jika key sudah exist, insert jika belum
INSERT INTO product_inventory (product_id, quantity)
VALUES (101, 50)
ON DUPLICATE KEY UPDATE
    quantity = quantity + VALUES(quantity),
    last_updated = NOW();

-- Berguna untuk upsert operations

UPDATE Operations

1. Basic Update

-- Update single column
UPDATE users
SET status = 'active'
WHERE id = 1;

-- Update multiple columns
UPDATE users
SET 
    status = 'verified',
    verified_at = NOW(),
    updated_at = NOW()
WHERE id = 1;

2. Update dengan Calculations

-- Update dengan arithmetic
UPDATE products
SET price = price * 1.1  -- Increase 10%
WHERE category = 'electronics';

-- Update dengan nilai dari kolom lain
UPDATE order_items
SET total_price = quantity * unit_price
WHERE total_price IS NULL;

3. Update dengan JOIN

-- Update dengan informasi dari table lain
UPDATE users u
INNER JOIN orders o ON u.id = o.user_id
SET u.total_orders = u.total_orders + 1
WHERE o.status = 'completed';

-- Update dengan subquery
UPDATE products
SET stock = stock - (
    SELECT SUM(quantity)
    FROM order_items
    WHERE product_id = products.id
      AND order_id IN (SELECT id FROM orders WHERE status = 'pending')
)
WHERE id IN (SELECT product_id FROM order_items);

4. Safe Update Practices

-- SELALU gunakan WHERE clause
-- DANGEROUS:
-- UPDATE users SET status = 'inactive';  -- Updates ALL users!

-- SAFE:
UPDATE users SET status = 'inactive' WHERE id = 123;

-- Gunakan LIMIT untuk batch updates
UPDATE large_table
SET processed = 1
WHERE processed = 0
LIMIT 1000;

DELETE Operations

1. Basic Delete

-- Delete single row
DELETE FROM users WHERE id = 123;

-- Delete dengan multiple conditions
DELETE FROM orders 
WHERE status = 'cancelled' 
  AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

2. DELETE dengan JOIN

-- Delete dengan referensi ke table lain
DELETE o FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.status = 'banned';

3. Safe Delete Practices

-- Verify sebelum delete dengan SELECT
SELECT * FROM users WHERE id = 123;
-- Jika benar, baru delete
DELETE FROM users WHERE id = 123;

-- Gunakan soft delete daripada hard delete
-- Tambahkan column:
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL;

-- Soft delete:
UPDATE users SET deleted_at = NOW() WHERE id = 123;

-- Query hanya active users:
SELECT * FROM users WHERE deleted_at IS NULL;

4. Batch Delete

-- Delete dalam chunks untuk large tables
DELETE FROM logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
LIMIT 1000;

-- Ulangi sampai tidak ada rows yang terhapus

REPLACE Statement

1. REPLACE INTO

-- Delete existing row jika key exist, kemudian insert
REPLACE INTO users (id, username, email)
VALUES (1, 'new_username', 'new@example.com');

-- Berguna untuk simple upsert tapi lebih berbahaya daripada INSERT...ON DUPLICATE

Batch Operations Best Practices

1. Transactions untuk Batch Operations

-- Gunakan transactions untuk data consistency
START TRANSACTION;

INSERT INTO orders (user_id, total_amount) VALUES (1, 100.00);
SET @order_id = LAST_INSERT_ID();

INSERT INTO order_items (order_id, product_id, quantity)
VALUES 
    (@order_id, 1, 2),
    (@order_id, 2, 1);

UPDATE products 
SET stock = stock - 2 WHERE id = 1;
UPDATE products 
SET stock = stock - 1 WHERE id = 2;

COMMIT;

-- Atau ROLLBACK jika ada error

2. Batching Large Inserts

# Python example untuk batch insert
import mysql.connector

def batch_insert(data_list, batch_size=1000):
    conn = mysql.connector.connect(host='localhost', database='mydb', user='user', password='pass')
    cursor = conn.cursor()
    
    query = "INSERT INTO users (username, email) VALUES (%s, %s)"
    
    for i in range(0, len(data_list), batch_size):
        batch = data_list[i:i+batch_size]
        cursor.executemany(query, batch)
        conn.commit()
        print(f"Inserted batch {i//batch_size + 1}")
    
    cursor.close()
    conn.close()

Kesimpulan

DML operations adalah foundation database manipulation:

INSERT: Tambahkan data baru
UPDATE: Modifikasi existing data
DELETE: Remove data (gunakan soft delete!)
REPLACE: Upsert dengan caution
Gunakan transactions untuk consistency

Selalu test di development sebelum production operations!