SQL REFERENCE

Data Manipulation Language (DML)

DML statements insert, update, and delete rows in Teide tables.

INSERT INTO ... VALUES

Inserts one or more rows with literal values.

INSERT INTO table_name (col1, col2, ...)
VALUES (val1, val2, ...),
       (val1, val2, ...),
       ...;

Examples

-- Insert a single row
INSERT INTO employees (id, name, department, salary)
VALUES (1, 'Alice', 'Engineering', 95000.00);

-- Insert multiple rows
INSERT INTO employees (id, name, department, salary)
VALUES (2, 'Bob', 'Marketing', 72000.00),
       (3, 'Carol', 'Engineering', 105000.00),
       (4, 'Dave', 'Sales', 68000.00);

-- Insert with NULL values
INSERT INTO employees (id, name, department, salary)
VALUES (5, 'Eve', NULL, 88000.00);

-- Insert with date/time types
INSERT INTO events (id, event_date, event_time)
VALUES (1, DATE '2024-06-15', TIME '09:30:00');

INSERT INTO ... SELECT

Inserts rows from the result of a query. The column count and types of the SELECT must match the target table.

INSERT INTO table_name (col1, col2, ...)
SELECT expr1, expr2, ...
FROM source_table
[WHERE condition];

Examples

-- Copy rows from one table to another
INSERT INTO archive (id, name, department, salary)
SELECT id, name, department, salary
FROM employees
WHERE active = FALSE;

-- Insert computed values
INSERT INTO summary (department, avg_salary)
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

-- Insert from CSV via subquery
INSERT INTO all_sales
SELECT * FROM read_csv('q4_sales.csv');

UPDATE ... SET ... WHERE

Modifies existing rows that match a condition. Without a WHERE clause, all rows are updated.

UPDATE table_name
SET col1 = expr1,
    col2 = expr2,
    ...
[WHERE condition];

Examples

-- Update a single column
UPDATE employees
SET salary = 100000.00
WHERE id = 1;

-- Update multiple columns
UPDATE employees
SET salary = salary * 1.10,
    department = 'Senior Engineering'
WHERE department = 'Engineering' AND salary > 90000;

-- Update all rows
UPDATE products
SET price = price * 1.05;

-- Update with expressions
UPDATE orders
SET total = quantity * unit_price,
    status = 'calculated'
WHERE status = 'pending';

DELETE FROM ... WHERE

Removes rows that match a condition. Without a WHERE clause, all rows are deleted.

DELETE FROM table_name
[WHERE condition];

Examples

-- Delete specific rows
DELETE FROM employees
WHERE id = 4;

-- Delete with complex condition
DELETE FROM orders
WHERE status = 'cancelled'
  AND order_date < DATE '2024-01-01';

-- Delete all rows (truncate)
DELETE FROM temp_results;

Using DML from Node.js

import { Context } from 'teide-js';

const ctx = new Context();

// Create and populate a table
ctx.sqlSync(`CREATE TABLE products (id INTEGER, name VARCHAR, price REAL)`);

ctx.sqlSync(`
  INSERT INTO products (id, name, price)
  VALUES (1, 'Widget', 9.99),
         (2, 'Gadget', 24.99),
         (3, 'Doohickey', 4.99)
`);

// Update prices
ctx.sqlSync(`UPDATE products SET price = price * 1.10 WHERE price < 10`);

// Delete a product
ctx.sqlSync(`DELETE FROM products WHERE id = 3`);

// Verify changes
const result = ctx.sqlSync(`SELECT * FROM products ORDER BY id`);
console.log(result.toArray());

ctx[Symbol.dispose]();
← DDL Queries →