🧩 DML
INSERT, UPDATE, DELETE and MERGE patterns for day-to-day data changes.
| Snippet | |
|---|---|
| ☆SELECT — Simplest form of a query |
SELECT * FROM employees;
|
| ☆INSERT — Insert a single row |
INSERT INTO dept (id, name) VALUES (1, 'HR');
|
| ☆UPDATE — Update matching rows |
UPDATE employees SET salary = 5000 WHERE id = 100;
|
| ☆DELETE — Delete matching rows |
DELETE FROM employees WHERE id = 100;
|
| ☆INSERT SELECT — Insert rows produced by a query |
INSERT INTO archive_employees SELECT * FROM employees WHERE status = 'INACTIVE';
|
| ☆MERGE — Upsert rows: update when matched, insert when not |
MERGE INTO employees e USING (SELECT 100 AS id, 7000 AS salary FROM dual) s ON (e.id = s.id) WHEN MATCHED THEN UPDATE SET e.salary = s.salary WHEN NOT MATCHED THEN INSERT (id, salary) VALUES (s.id, s.salary);
|
| ☆DELETE EXISTS — Delete rows that have a matching row in another table |
DELETE FROM employees e WHERE EXISTS (SELECT 1 FROM dept d WHERE d.id = e.dept_id AND d.status = 'CLOSED');
|
| ☆UPDATE JOIN — Update rows filtered by a related table |
UPDATE employees e SET e.salary = e.salary * 1.1 WHERE e.dept_id IN (SELECT d.id FROM dept d WHERE d.location = 'NY');
|
| ☆INSERT ALL — Multi-row multi-table insert in a single statement |
INSERT ALL INTO dept (id, name) VALUES (1, 'HR') INTO dept (id, name) VALUES (2, 'IT') SELECT * FROM dual;
|