Modifying Data in SQL

Modifying Data in SQL – Insert, Update, and Delete Records Safely

In the previous blogs, we learned how to retrieve, filter, summarize, and join data. But databases aren’t just for reading – they’re also for modifying data. In this blog, we’ll explore how to safely insert, update, and delete records in SQL, while minimizing risks of mistakes.

1. Inserting Data with INSERT

The INSERT statement adds new records to a table.

Example: Adding a New Employee

INSERT INTO employees (first_name, last_name, department_id, salary)
VALUES ('Alice', 'Johnson', 2, 60000);

  • Adds a new employee named Alice in department 2 with a salary of 60,000.

  • Column order matters – values must match the specified columns.

Tip:

You can insert multiple rows in a single query:

INSERT INTO employees (first_name, last_name, department_id, salary)
VALUES 
('Bob', 'Lee', 1, 55000),
('Clara', 'Ng', 3, 70000);

2. Updating Data with UPDATE
The UPDATE statement modifies existing records. 
Always use a WHERE clause to avoid updating all rows accidentally.
Example: Give a Raise to a Specific Employee
UPDATE employees
SET salary = 65000
WHERE first_name = 'Alice' AND last_name = 'Johnson';
  • Updates only Alice Johnson’s salary.
Updating Multiple Rows
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 2;
  • Gives a 10% raise to all employees in department 2.
ELETE statement removes records from a table. 
Like UPDATE, always use WHERE to avoid deleting everything.
Example: Remove an Employee
DELETE FROM employees
WHERE first_name = 'Bob' AND last_name = 'Lee';
  • Deletes Bob Lee from the table.
Deleting Multiple Rows
DELETE FROM employees
WHERE department_id = 3;
  • Deletes all employees in department 3.
4. Using Transactions for Safety
A transaction ensures that multiple operations are completed safely. 
If something goes wrong, you can rollback changes.
BEGIN;

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 2;

DELETE FROM employees
WHERE department_id = 3;

COMMIT;  -- Save changes
  • Use ROLLBACK; instead of COMMIT; if you want to cancel changes.
  • Transactions are essential in production databases to prevent accidental data loss.
Best Practices
  • Always backup data before bulk updates or deletes.
  • Use WHERE clauses carefully.
  • Prefer transactions for multiple operations.
  • Test queries on small datasets first.
Modifying data in SQL is powerful but requires caution. 
With INSERT, UPDATE, and DELETE along with transactions, you can:
  • Add new records safely.
  • Update existing data efficiently.
  • Remove unwanted data without mistakes.

Comments

Post a Comment

Popular posts from this blog

Database Integration in FastAPI (SQLAlchemy CRUD)

  Introduction So far, we’ve built APIs without storing data.But real-world applications need a database to: Store user data. Manage products. Persist application state. In this blog, we’ll connect FastAPI with a database using SQLAlchemy and perform CRUD operations . What is SQLAlchemy? SQLAlchemy is a powerful Python ORM (Object Relational Mapper) that allows you to: Interact with databases using Python code. Avoid writing raw SQL queries. Work with models instead of tables. Step 1: Install Dependencies pip install sqlalchemy (For SQLite, no extra driver needed) Project Structure Update app/ ├── main.py ├── database/ │ ├── connection.py ├── models/ │ ├── item.py ├── schemas/ │ ├── item.py ├── routes/ │ ├── item.py Step 2: Database Connection app/database/connection.py from sqlalchemy import create_engine from sqlalchemy . orm import sessionmaker , declarative_base DATABASE_URL = "sqlite:///./test.db" engine = create_engine ( DAT...
Read more

Middleware & CORS in FastAPI

  Introduction As your API grows, you may need to: Log requests Modify responses Handle cross-origin requests This is where Middleware and CORS (Cross-Origin Resource Sharing) come into play.They help control how requests and responses flow through your application. What is Middleware? Middleware is a function that runs: Before a request reaches your route After a response is returned Think of it as a pipeline layer between client and server. Request Flow with Middleware Client → Middleware → Route → Middleware → Response Creating Custom Middleware from fastapi import FastAPI , Request import time app = FastAPI () @ app . middleware ( "http" ) async def log_requests ( request : Request , call_next ): start_time = time . time() response = await call_next ( request ) process_time = time . time() - start_time print ( f"Request: { request . url} | Time: { process_time } " ) return response What This Middle...
Read more

Python Data Handling

Python Data Handling for Beginners In Python, data handling means storing, changing, and using data in different ways. In this post, you will learn: Lists Tuples Dictionaries Sets String Methods Type Conversion 1. Python Lists :Lists store many values in one variable.                fruits = ["apple", "banana", "mango"]                                                                       Method                          Use                                            append()          ...
Read more