Scribd
Upload
2 views12 pages

03_SQL_Insert_Update_Delete

3. ✏️ SQL INSERT, UPDATE, DELETE & Transactions What it teaches: How to change information safely. You'll learn: INSERT UPDATE DELETE Multiple-row inserts Primary-key generation Constraints Transactions COMMIT ROLLBACK ACID concepts Safe database-change procedures Common mistakes that can accidentally modify thousands of records You'll learn why this is dangerous: UPDATE Customers SET city = 'Toronto'; versus the much safer: UPDATE Customers SET city = 'Toronto' WHERE customer_id = 15; G

Uploaded by

shawnreeves2000
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views12 pages

03_SQL_Insert_Update_Delete

3. ✏️ SQL INSERT, UPDATE, DELETE & Transactions What it teaches: How to change information safely. You'll learn: INSERT UPDATE DELETE Multiple-row inserts Primary-key generation Constraints Transactions COMMIT ROLLBACK ACID concepts Safe database-change procedures Common mistakes that can accidentally modify thousands of records You'll learn why this is dangerous: UPDATE Customers SET city = 'Toronto'; versus the much safer: UPDATE Customers SET city = 'Toronto' WHERE customer_id = 15; G

Uploaded by

shawnreeves2000
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL INSERT, UPDATE, DELETE and

Transactions
Beginner-friendly learning guide
1. INSERT

Adding rows
INSERT creates new records. Name the columns explicitly.

Multiple rows
Many DBMS products allow several values groups in one INSERT.

INSERT INTO Students (student_id,name,age)


VALUES (1,'Maya',19);

INSERT INTO Students (student_id,name,age)


VALUES (2,'Liam',21),(3,'Noah',20);
2. UPDATE

Changing values
UPDATE modifies existing rows.

WHERE matters
Without WHERE, every row can be changed. Test the filter with SELECT first.

SELECT * FROM Students WHERE student_id=1;


UPDATE Students SET age=20 WHERE student_id=1;
3. DELETE

Removing rows
DELETE removes records that match its filter.

Safety
A DELETE without WHERE can remove all rows.

SELECT * FROM Students WHERE student_id=3;


DELETE FROM Students WHERE student_id=3;
4. Transactions

Atomic work
A transaction groups related changes.

COMMIT and ROLLBACK


COMMIT makes changes permanent; ROLLBACK reverses uncommitted changes where supported.

BEGIN;
UPDATE Accounts SET balance=balance-100 WHERE account_id=1;
UPDATE Accounts SET balance=balance+100 WHERE account_id=2;
COMMIT;
5. ACID

Atomicity
All-or-nothing behavior.

Consistency
Rules remain valid.

Isolation
Concurrent work is controlled.

Durability
Committed work persists.
6. Constraints

NOT NULL
Requires a value.

UNIQUE
Prevents duplicate values.

CHECK
Requires a condition.

Keys
PRIMARY KEY identifies rows; FOREIGN KEY protects relationships.

CREATE TABLE Users (


user_id INTEGER PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
age INTEGER CHECK(age>=13)
);
7. Generated IDs

Why use IDs


Generated identifiers are stable and independent of changing business information.

DBMS differences
PostgreSQL identity, MySQL AUTO_INCREMENT, SQL Server IDENTITY, and SQLite approaches
differ.

CREATE TABLE Customers (


customer_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
8. Safe Change Procedure

Step 1
Verify backups or recovery options.

Step 2
Run a SELECT with the intended WHERE clause.

Step 3
Check affected rows.

Step 4
Use a transaction when appropriate.

Step 5
Commit only after verification.
9. Common Mistakes

Mistake
Forgetting WHERE, using the wrong key, inserting invalid types, ignoring constraints, and assuming
every DBMS uses identical syntax.

Prevention
Read the query aloud, test on sample data, and inspect the result.
10. Practice and Review

Exercises
Insert three products, update one price, delete one test record, create a transaction, and design
constraints for a Users table.

Questions
Why is WHERE important? What does ROLLBACK do? Why are constraints useful? When should a
transaction be used?
Extended safety practice
Before running a data-changing statement, imagine the worst-case result. If the WHERE clause were
accidentally removed, would every row change? If the filter were too broad, which records would be
affected? Practice using SELECT to preview the target set. In a transaction, verify the result before
COMMIT whenever your workflow permits it. Keep training data separate from important data, and
learn the backup and recovery procedures of your database system before performing large
changes.

You might also like