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 | Monique@someSchool.edu |
| Teri Gutierrez | 5 | Teri@someSchool.edu |
| Spencer Pautier | 8 | Spencer@someSchool.edu |
| Louis Ramsey | 6 | Louis@someSchool.edu |
| Alvin Greene | 6 | Alvin@someSchool.edu |
| Sophie Freeman | 7 | Sophie@someSchool.edu |
| Edgar Frank "Ted" Codd | 6 | Edgar@someSchool.edu |
| Donald D. Chamberlin | 7 | Donald@someSchool.edu |
| Raymond F. Boyce | 8 | Raymond@someSchool.edu |
+------------------------+--------------------+------------------------+
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 | Monique@someSchool.edu |
| Teri Gutierrez | 800 | Programming | Teri@someSchool.edu |
| Spencer Pautier | 1000 | Programming | Spencer@someSchool.edu |
| Louis Ramsey | 1200 | Programming | Louis@someSchool.edu |
| Alvin Greene | 1200 | Programming | Alvin@someSchool.edu |
| Sophie Freeman | 1200 | Programming | Sophie@someSchool.edu |
| Edgar Frank "Ted" Codd | 2400 | Computer Science | Edgar@someSchool.edu |
| Donald D. Chamberlin | 2400 | Computer Science | Donald@someSchool.edu |
| Raymond F. Boyce | 2400 | Computer Science | Raymond@someSchool.edu |
+------------------------+-----------+------------------+------------------------+
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.
How do you use an Update statement?
To update a record in a table you use the UPDATE statement.
Be careful. You can update all records of the table or just a few. Use the WHERE condition to specify which records do you want to update. It is possible to update one or more columns at a time. The syntax is:
UPDATE table_name
SET column1 = value1,
column2 = value2, ...
WHERE condition;
Here is an example updating the Name of the record with Id 4:
UPDATE Person
SET Name = “Elton John”
WHERE Id = 4;
You can also update columns in a table by using values from other tables. Use JOIN clause to get data from multiple tables. The syntax is:
UPDATE table_name1
SET table_name1.column1 = table_name2.columnA
table_name1.column2 = table_name2.columnB
FROM table_name1
JOIN table_name2 ON table_name1.ForeignKey = table_name2.Key
Here is an example updating Manager of all records:
UPDATE Person
SET Person.Manager = Department.Manager
FROM Person
JOIN Department ON Person.DepartmentID = Department.ID