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 Email
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 Email
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!