INSERT Statement for Data Insertion

The INSERT statement allows adding new records into database tables. It supports multiple usage scenarios:

• Inserting complete rows
• Inserting partial data for specific columns
• Bulk insertion of multiple records
• Combining with SELECT for result-based insertion

Inserting Without Column Specification

For full row insertion:

INSERT INTO table_name VALUES (value1, value2, value3,...);

Data must match table structure including:

• Column order definition
• Data type compatibility
• Constraint requirements (defaluts, NULL allowance, primary key, auto-increment)

Check table structure using:

DESC table_name;

Bulk Insertion of Multiple Records

INSERT INTO table_name VALUES 
(value1), (value2), (value3);

Column-Specific Insertion

Single record insertion:

INSERT INTO database_name.table_name (column1, column2) 
VALUES (value1, value2);

Multiple records insertion:

INSERT INTO database_name.table_name (column1, column2)
VALUES (value1, value2), (value3, value4);

SET Clause Insertion

INSERT INTO database_name.table_name 
SET column1 = value1, column2 = value2;

UPDATE Statement for Data Modification

Suports two modification scopes:

• Targeted updates for specific rows
• Global updates for all records

Basic Syntax

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

Bulk Update Example

UPDATE database_name.table_name 
SET column1 = value1, column2 = value2;

Conditional Update

UPDATE database_name.table_name 
SET column1 = value1, column2 = value2 
WHERE specific_condition;

DELETE Statement for Data Removal

Standard Syntax

DELETE FROM database_name.table_name 
WHERE deletion_condition;

Complete Table Truncation

DELETE FROM database_name.table_name;

Or use the TRUNCATE command:

TRUNCATE TABLE database_name.table_name;

Practical Examples

1. Inserting a new customer record:

INSERT INTO customers (
    cust_id,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country,
    cust_email
) VALUES (
    10042,
    'Qzb',
    '123 Main Street',
    'Oak Park',
    'MI',
    '48237',
    'China',
    'ben@fortabbwpc.wpcomstaging.com'
);

2. Creating table backups:

CREATE TABLE ordersBackup AS SELECT * FROM orders;
CREATE TABLE orderitemsBackup AS SELECT * FROM orderitems;

3. Standardizing US state abbreviations:

UPDATE vendors 
SET vend_state = UPPER(vend_state) 
WHERE vend_country = 'USA';

UPDATE customers 
SET cust_state = UPPER(cust_state) 
WHERE cust_country = 'USA';

4. Removing a specific record:

DELETE FROM customers 
WHERE cust_id = 10042;