A guide to the SQL Update Query

A guide to the SQL Update Query
0

What an Update query can do

An update query gives the DBA or SQL-using programmer the ability to update many records with one command.

Important Safety Tip! Always have a backup copy of what you are about to change BEFORE you change it!

This guide will:

  • add a new field to the student table
  • test the logic to update that field with a school assigned email address
  • update the new field.

Here is the student table as we start this process

SELECT * FROM student;
+-----------+------------------------+-----------+------------------+---------------------+---------------------+
| studentID | FullName               | sat_score | programOfStudy   | rcd_Created         | rcd_Updated         |
+-----------+------------------------+-----------+------------------+---------------------+---------------------+
|         1 | Monique Davis          |       400 | Literature       | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 |
|         2 | Teri Gutierrez         |       800 | Programming      | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 |
|         3 | Spencer Pautier        |      1000 | Programming      | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 |
|         4 | Louis Ramsey           |      1200 | Programming      | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 |
|         5 | Alvin Greene           |      1200 | Programming      | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 |
|         6 | Sophie Freeman         |      1200 | Programming      | 2017-08-16 15:34:50 | 2017-08-16 15:34:50 |
|         7 | Edgar Frank "Ted" Codd |      2400 | Computer Science | 2017-08-16 15:35:33 | 2017-08-16 15:35:33 |
|         8 | Donald D. Chamberlin   |      2400 | Computer Science | 2017-08-16 15:35:33 | 2017-08-16 15:35:33 |
|         9 | Raymond F. Boyce       |      2400 | Computer Science | 2017-08-16 15:35:33 | 2017-08-16 15:35:33 |
+-----------+------------------------+-----------+------------------+---------------------+---------------------+
9 rows in set (0.00 sec)

Alter the table and add a new field

    ALTER TABLE `fcc_sql_guides_database`.`student` 
	ADD COLUMN `schoolEmailAdr` VARCHAR(125) NULL AFTER `programOfStudy`;

The student table after the alter is executed.

mysql> SELECT FullName, sat_score, programOfStudy, schoolEmailAdr FROM student;
+------------------------+-----------+------------------+----------------+
| FullName               | sat_score | programOfStudy   | schoolEmailAdr |
+------------------------+-----------+------------------+----------------+
| Monique Davis          |       400 | Literature       | NULL           |
| Teri Gutierrez         |       800 | Programming      | NULL           |
| Spencer Pautier        |      1000 | Programming      | NULL           |
| Louis Ramsey           |      1200 | Programming      | NULL           |
| Alvin Greene           |      1200 | Programming      | NULL           |
| Sophie Freeman         |      1200 | Programming      | NULL           |
| Edgar Frank "Ted" Codd |      2400 | Computer Science | NULL           |
| Donald D. Chamberlin   |      2400 | Computer Science | NULL           |
| Raymond F. Boyce       |      2400 | Computer Science | NULL           |
+------------------------+-----------+------------------+----------------+
9 rows in set (0.00 sec)

TESTING the logic (VERY important step!)

SELECT FullName, instr(FullName," ") AS firstSpacePosition, 
concat(substring(FullName,1,instr(FullName," ")-1),"@someSchool.edu") AS schoolEmail
FROM student;
+------------------------+--------------------+------------------------+
| FullName               | firstSpacePosition | schoolEmail            |
+------------------------+--------------------+------------------------+
| Monique Davis          |                  8 | [email protected] |
| Teri Gutierrez         |                  5 | [email protected]    |
| Spencer Pautier        |                  8 | [email protected] |
| Louis Ramsey           |                  6 | [email protected]   |
| Alvin Greene           |                  6 | [email protected]   |
| Sophie Freeman         |                  7 | [email protected]  |
| Edgar Frank "Ted" Codd |                  6 | [email protected]   |
| Donald D. Chamberlin   |                  7 | [email protected]  |
| Raymond F. Boyce       |                  8 | [email protected] |
+------------------------+--------------------+------------------------+
9 rows in set (0.00 sec)

A note about concat(): in MySQL this command is used to combined strings, not so in other SQL versions (check your manual). In this usage it works like this: The substring of the FullName field up to but not including the first space is combined with “@someSchool.edu”. In the real world this would HAVE TO be much more complex and you would need to ensure that the email address is unique.

Doing the update

We’ll pretend that this is what we want and update the table with this information:

UPDATE student SET schoolEmailAdr = concat(substring(FullName,1,instr(FullName," ")-1),"@someSchool.edu")
WHERE schoolEmailAdr is NULL;

Success!

mysql> SELECT FullName, sat_score, programOfStudy, schoolEmailAdr FROM student;
+------------------------+-----------+------------------+------------------------+
| FullName               | sat_score | programOfStudy   | schoolEmailAdr         |
+------------------------+-----------+------------------+------------------------+
| Monique Davis          |       400 | Literature       | [email protected] |
| Teri Gutierrez         |       800 | Programming      | [email protected]    |
| Spencer Pautier        |      1000 | Programming      | [email protected] |
| Louis Ramsey           |      1200 | Programming      | [email protected]   |
| Alvin Greene           |      1200 | Programming      | [email protected]   |
| Sophie Freeman         |      1200 | Programming      | [email protected]  |
| Edgar Frank "Ted" Codd |      2400 | Computer Science | [email protected]   |
| Donald D. Chamberlin   |      2400 | Computer Science | [email protected]  |
| Raymond F. Boyce       |      2400 | Computer Science | [email protected] |
+------------------------+-----------+------------------+------------------------+
9 rows in set (0.00 sec)

As with all of these SQL things there is MUCH MORE to them than what’s in this introductory guide.

I hope this at least gives you enough to get started.

Please see the manual for your database manager and have fun trying different options yourself.