UPDATE data into DATABASE in SQL

SQL

The SQL UPDATE statement is a component of data manipulation language (DML) in SQL. It allows us to modify existing records within a database table. Whether we need to correct errors, update outdated information, or make changes based on specific conditions, the UPDATE statement manage and manipulate data effectively.

Basics of SQL UPDATE

Syntax:

The basic syntax of the SQL UPDATE statement is as follows:

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

table_name: The name of the table you want to update.
SET: Specifies the columns to be modified and their new values.
WHERE: Defines the conditions to identify the rows to be updated. If omitted, all rows in the table will be updated.

Practical Example: Updating Student Records in a Database

Consider the following database table called Students:

student_id	first_name	last_name	age	grade
1	John	Doe	20	85
2	Jane	Smith	22	92
3	Mike	Johnson	21	78

Example 1: Basic Update

Let’s update John Doe’s age to 21.

UPDATE Students
SET age = 21
WHERE student_id = 1;

Expected Output:

student_id	first_name	last_name	age	grade
1	John	Doe	21	85
2	Jane	Smith	22	92
3	Mike	Johnson	21	78

Example 2: Conditional Update

Increase the grade of students below 80 by 5 points.

UPDATE Students
SET grade = grade + 5
WHERE grade < 80;

Expected Output:

student_id	first_name	last_name	age	grade
1	John	Doe	21	90
2	Jane	Smith	22	92
3	Mike	Johnson	21	83

Example 3: Multiple Column Update

Update Jane Smith’s age and grade simultaneously.

UPDATE Students
SET age = 23, grade = 95
WHERE student_id = 2;

Expected Output:

student_id	first_name	last_name	age	grade
1	John	Doe	21	90
2	Jane	Smith	23	95
3	Mike	Johnson	21	83

Example 4: Updating Multiple Columns Based on a Condition

Suppose you want to increase the grade of students below the age of 22 by 10 points:

UPDATE Students
SET grade = grade + 10
WHERE age < 22;

Expected Output:

student_id	first_name	last_name	age	grade
1	John	Doe	21	95
2	Jane	Smith	22	92
3	Mike	Johnson	21	88

Example 5: Updating Records with Conditional String Modification

Suppose you want to add a prefix ‘Mr.’ to the first names of male students:

UPDATE Students
SET first_name = CONCAT('Mr. ', first_name)
WHERE gender = 'Male';

Expected Output:

student_id	first_name	last_name	age	grade	gender
1	Mr. John	Doe	21	95	Male
2	Jane	Smith	22	92	Female
3	Mr. Mike	Johnson	21	88	Male

Example 6: Updating Records with NULL Values

If you want to update the age of students with NULL values to 20

UPDATE Students
SET age = 20
WHERE age IS NULL;

Expected Output:

student_id	first_name	last_name	age	grade	gender
1	Mr. John	Doe	21	95	Male
2	Jane	Smith	22	92	Female
3	Mr. Mike	Johnson	21	88	Male