SQL Update

SQL Update

This tutorial covers SQL Update statement.

In SQL, records can be modified. This is the function of the “update” statement.

The syntax is easy to understand and the keywords are “update set” (with the addition of the “where” statement).

The SQL “where” statement is explored in further tutorials. Simply, its functionality is to filter data (select specific data).

This page encompasses:

• updating records
• updating multiple records

Let’s use the previously created table “student_info“.

id	firstName	lastName	age
1	John	Smith	21
2	Patrick	Casey	26
3	Sam	Adams	19
4	Mike	Garcia	NULL

Update records

As mentioned above, the “where” statement selects the specified data.

In this instance, it chooses where to update the records. The “where” statement is extremely important when updating. In the event of not specifying which records to update, the “update” statement will update all records within the table.

You can select any information as a filter, but if different cells have the same data, all will update. For this reason, it is a good practice to use the unique id of the row when updating a single record row.

Update records without “where”

Let’s check what happens when we do not use the “where” statement.

UPDATE student_info
SET firstName='Peter', lastName='Bernard', age=20;

id	firstName	lastName	age
1	Peter	Bernard	20
2	Peter	Bernard	20
3	Peter	Bernard	20
4	Peter	Bernard	20

Update records with “where”

Let’s update the table with the “where” statement.

Below is an example. Please note that the “age” column does not change.

UPDATE student_info
SET firstName='Peter', lastName='Bernard'
WHERE id=1;

If no errors, the output produces “1 row affected“. Let’s display the result with the “SELECT * FROM student_info;“.

id	firstName	lastName	age
1	Peter	Bernard	21
2	Patrick	Casey	26
3	Sam	Adams	19
4	Mike	Garcia	NULL

The first student (previously John Smith) is now Peter Bernard. The age stays the same, as it was not specified in the “update” statement.

Update multiple records

In the above scenario, we select the “id“, which is a unique data.

Sometimes, we may need to update multiple rows simultaneously. For this scenario, we add a 5th student (Patrick Dumont, 21). This creates partial duplicates (two students with the same name, Patrick).

The table is below.

id	firstName	lastName	age
1	Peter	Bernard	21
2	Patrick	Casey	26
3	Sam	Adams	19
4	Mike	Garcia	NULL
5	Patrick	Dumont	21

Now let’s update the records, using the “where” statement with the name Patrick (two students have the same name).

UPDATE student_info
SET firstName='Jason', lastName='Brown', age=35
WHERE firstName='Patrick';

2 rows affected. Note that here we update the age as well.

id	firstName	lastName	age
1	Peter	Bernard	21
2	Jason	Brown	35
3	Sam	Adams	19
4	Mike	Garcia	NULL
5	Jason	Brown	35

After the update, the second and fifth student has the same name and age, Jason Brown (35).

---

Next: SQL Delete