SQL Data Modification: INSERT, UPDATE, DELETE

1. INSERT Statement

The INSERT statement is used to add new records to a table.

Basic Syntax

Edit Copy

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

Example

To insert a new product into a products table:

Edit Copy

INSERT INTO products (name, category_id, price)
VALUES ('Smartphone', 1, 699.99);

2. UPDATE Statement

The UPDATE statement is used to modify existing records in a table.

Basic Syntax

Edit Copy

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

Example

To update the price of a product with a specific ID in the products table:

Edit Copy

UPDATE products
SET price = 649.99
WHERE id = 1;

3. DELETE Statement

The DELETE statement is used to remove records from a table.

Basic Syntax

Edit Copy

DELETE FROM table_name
WHERE condition;

Example

To delete a product from the products table:

Edit Copy

DELETE FROM products
WHERE id = 1;

4. Important Considerations

When using data modification statements, consider the following:

• Transactions: It is a good practice to use transactions for critical operations to maintain data integrity.
• WHERE Clause: Always use the WHERE clause in UPDATE and DELETE statements to avoid unintended data loss.
• Backup: Consider backing up your data before performing mass updates or deletions.

5. Conclusion

Data modification is a crucial aspect of managing SQL databases. Understanding how to effectively use INSERT, UPDATE, and DELETE statements will help you maintain and manipulate your data efficiently.