SQL (pronounced Seequel) stands for Structured Query Language. It is a strongly typed, static (types are checked before runtime) querying language that first appeared in 1974 (woah, 46 years old!), but was not initially released until 1986.
You might be thinking to yourself that such an "old" tool has its best days behind it, but you'd be far from correct. In 2019, through the Scale Grid DeveloperWeek survey, SQL was used by 60.5% of respondents, while NoSQL was used by only 39.5 % of respondents.
To be clear, the SQL category was broken down into several subcategories that included MySQL, PostgreSQL, SQL Server, and so on, while the NoSQL category was broken apart into subcategories that contained MongoDB, Cassandra, etc.
Even in 2017, according to the Stack Overflow Developer's Survey, the second most popular language used was SQL (right behind JavaScript) with 50% of the 64,000 respondents saying they still use SQL in some form.
It's popularity is due, at least in part, to the simplicity of the language, the fact that it was built with relational data in mind, and because it's proven itself as reliable for searching, joining, and filtering data.
Suffice it to say, SQL is not only alive and kicking, but thriving among today's development community.
Now let's see why!
The Fun Parts
SQL Server is the preferred flavor of SQL that I use in my day to day activities at work, so the examples below will conform to those standards.
One thing I find myself doing a great deal of is updating multiple records within a table. Now I could do this one record at a time but SQL gives us the ability to update multiple (thousands upon thousands if need be) records at once through the UPDATE
statement.
The UPDATE
statement can be used to update a single column, a larger set of records (through the use of conditions), and/or the entire table in a database. The condition(s) can be a boolean, a string check, or mathematical sequence that resolves to a boolean (greater than, less than, etc.).
While it may vary slightly from flavor to flavor, the general syntax is as follows:
UPDATE table-name
SET column-name = value[, column-name=value]
[WHERE condition]
The brackets ( [] ) above denote optional additions to the query.
***It is very important to note that without a WHERE
condition, ALL records in the table will be updated as soon as you execute the query.***
Example Queries
As our dataset, I'll be using this table named Work_Tickets:
SalesOrderNum | WorkTicketNum | Customer_Code | Customer_Contact | UnitCost | Billed | ParentLineKey | Qty_Ordered | Qty_Shipped |
00061356 | 000931 | 1250 | sales@wayneindustries.com | 0.00 | False | 079777 | 12.0 | 0 |
00061357 | 000932 | 1251 | contact@starkindustries.com | 0.00 | False | 085695 | 196.5 | 0 |
00061358 | 000933 | 1252 | animation@acmetoons.com | 0.00 | False | 085569 | 17.5 | 0 |
Simple Query Without Conditions
Here is a very simple update query that will change all of the UnitCost
fields to the number 131.6152
:
UPDATE Work_Tickets
SET UnitCost = 131.6152
Note there is no WHERE
clause, so every line in the table will be updated and our dataset will now look like this:
SalesOrderNum | WorkTicketNum | Customer_Code | Customer_Contact | UnitCost | Billed | ParentLineKey | Qty_Ordered | Qty_Shipped |
00061356 | 000931 | 1250 | sales@wayneindustires.com | 131.6152 | False | 079777 | 12.0 | 0 |
00061357 | 000932 | 1251 | contact@starkindustries.com | 131.6152 | False | 085695 | 196.5 | 0 |
00061358 | 000933 | 1252 | animation@acmetoons.com | 131.6152 | False | 085569 | 17.5 | 0 |
Simple Queries With Condition(s)
Here is a simple query with one condition statement:
UPDATE Work_Tickets
SET Billed = true
WHERE UnitCost <> 0.00
This query will update the Billed
field to be true on every line that matches the condition of the UnitCost
not equaling 0. After we run our query, the dataset will look like this:
SalesOrderNum | WorkTicketNum | Customer_Code | Customer_Contact | UnitCost | Billed | ParentLineKey | Qty_Ordered | Qty_Shipped |
00061356 | 000931 | 1250 | sales@wayneindustires.com | 131.6152 | True | 079777 | 12.0 | 0 |
00061357 | 000932 | 1251 | contact@starkindustries.com | 131.6152 | True | 085695 | 196.5 | 0 |
00061358 | 000933 | 1252 | animation@acmetoons.com | 131.6152 | True | 085569 | 17.5 | 0 |
Below is a query where we change the ParentLineKey
to the string 000134
where the SalesOrderNum
and the WorkTicketNum
both match the given strings.
UPDATE Work_Tickets
SET ParentLineKey = 000134
WHERE SalesOrderNum = 00061358 and WorkTicketNumber = 000933
So, the 085569 in the ParentLineKey
field will be replaced with 000134
and our dataset now looks like this:
SalesOrderNum | WorkTicketNum | Customer_Code | Customer_Contact | UnitCost | Billed | ParentLineKey | Qty_Ordered | Qty_Shipped |
00061356 | 000931 | 1250 | sales@wayneindustires.com | 131.6152 | True | 079777 | 12.0 | 0 |
00061357 | 000932 | 1251 | contact@starkindustries.com | 131.6152 | True | 085695 | 196.5 | 0 |
00061358 | 000933 | 1252 | animation@acmetoons.com | 131.6152 | True | 000134 | 17.5 | 0 |
Updating Multiple Fields
Let's say you have a much larger dataset than the one we are currently using and you have several fields to update.
It would be tedious and mind-numbing to update them with different update statements. Luckily for us it's also possible to update several fields at once with an update statement, as long as we separate the column names with a comma:
UPDATE Work_Tickets
SET UnitCost = 129.8511, Qty_Ordered = 72, Qty_Shipped = 72
WHERE SalesOrderNum = 00061358
And here is the result with the updated fields after running the query:
SalesOrderNum | WorkTicketNum | Customer_Code | Customer_Contact | UnitCost | Billed | ParentLineKey | Qty_Ordered | Qty_Shipped |
00061356 | 000931 | 1250 | sales@wayneindustires.com | 131.6152 | True | 079777 | 12.0 | 0 |
00061357 | 000932 | 1251 | contact@starkindustries.com | 131.6152 | True | 085695 | 196.5 | 0 |
00061358 | 000933 | 1252 | animation@acmetoons.com | 129.8511 | True | 000134 | 72 | 72 |
Using Update in a Subquery
The above examples are perfect if you are working with one data source. However, most of your data will not be stored in a single table. That's where using UPDATE with multiple data sources comes in handy.
The syntax for updating a column/table changes a little if we want to bring in data from another table:
UPDATE table-name
SET column-name = (SELECT column name(s)
FROM table2-name
WHERE condition(s))
[WHERE condition]
And here are the two tables we'll be using for this query - the Work_Tickets table:
SalesOrderNum | WorkTicketNum | Customer_Code | Customer_Contact | UnitCost | Billed | ParentLineKey | Qty_Ordered | Qty_Shipped |
00061356 | 000931 | 1250 | sales@wayneindustires.com | 131.6152 | True | 079777 | 12.0 | 0 |
00061357 | 000932 | 1251 | contact@starkindustries.com | 131.6152 | True | 085695 | 196.5 | 0 |
00061358 | 000933 | 1252 | animation@acmetoons.com | 129.8511 | True | 000134 | 72 | 72 |
and the Customer_Info table :
Name | Industry | Code | Address | City | Discount | PhoneNumber | |
Wayne Enterprises | Defense,weaponry,aerospace,enginerring | NULL | 1631 Dark Knight Way | Gotham | 19.75 | 5556614000 | sales@wayneindustires.com |
Stark Industries | Defense,weaponry,protection | 1251 | 5641 Iron Dr | Undisclosed | 19.73 | 9993126156 | contact@starkindustries.com |
Acme Corp | Comedy,laughter,animation | 1252 | 24569 Smiling St | Toon Town | 17.53 | 3216549877 | animation@acmetoons.com |
The UPDATE
statement with a subquery looks like this:
UPDATE Customer_Info
SET Code = (SELECT Customer_Code
FROM Work_Tickets
WHERE Work_Tickets.Customer_Contact = Customer_Info.Email)
FROM Work_Tickets
WHERE Code IS NULL
This example will update the Code field on the Customer_Info table where the email address match from both tables. And this is what our Customer_Info table looks like now:
Name | Industry | Code | Address | City | Discount | PhoneNumber | |
Wayne Enterprises | Defense,weaponry,aerospace,enginerring | 1250 | 1631 Dark Knight Way | Gotham | 19.75 | 5556614000 | sales@wayneindustires.com |
Stark Industries | Defense,weaponry,protection | 1251 | 5641 Iron Dr | Undisclosed | 19.73 | 9993126156 | contact@starkindustries.com |
Acme Corp | Comedy,laughter,animation | 1252 | 24569 Smiling St | Toon Town | 17.53 | 3216549877 | animation@acmetoons.com |
Wrapping up
I hope this article has been helpful to you in understanding how the UPDATE statement works in SQL.
You're now ready to write your own SQL UPDATE statements like a champ! After you do, I'd love for you to share them with me on social media!
Don't forget to check out my blog where I frequently post articles about web development.
While you're there why not sign up for my newsletter? You can do that at the top right of the main blog page. I like to send out interesting articles (mine and others), resources, and tools for developers every now and then.
If you have questions about this article or just in general my DMs are open – come say hi on Twitter or any of my other social media accounts which you can find below the newsletter sign up on the main page of my blog or on my profile here at fCC :)
Have an awesome day and happy coding, friend!