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:

SalesOrderNumWorkTicketNumCustomer_CodeCustomer_ContactUnitCostBilledParentLineKeyQty_OrderedQty_Shipped
000613560009311250sales@wayneindustries.com0.00False07977712.00
000613570009321251contact@starkindustries.com0.00False085695196.50
000613580009331252animation@acmetoons.com0.00False08556917.50

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:

SalesOrderNumWorkTicketNumCustomer_CodeCustomer_ContactUnitCostBilledParentLineKeyQty_OrderedQty_Shipped
000613560009311250sales@wayneindustires.com131.6152False07977712.00
000613570009321251contact@starkindustries.com131.6152False085695196.50
000613580009331252animation@acmetoons.com131.6152False08556917.50

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:

SalesOrderNumWorkTicketNumCustomer_CodeCustomer_ContactUnitCostBilledParentLineKeyQty_OrderedQty_Shipped
000613560009311250sales@wayneindustires.com131.6152True07977712.00
000613570009321251contact@starkindustries.com131.6152True085695196.50
000613580009331252animation@acmetoons.com131.6152True08556917.50

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:

SalesOrderNumWorkTicketNumCustomer_CodeCustomer_ContactUnitCostBilledParentLineKeyQty_OrderedQty_Shipped
000613560009311250sales@wayneindustires.com131.6152True07977712.00
000613570009321251contact@starkindustries.com131.6152True085695196.50
000613580009331252animation@acmetoons.com131.6152True00013417.50

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:

SalesOrderNumWorkTicketNumCustomer_CodeCustomer_ContactUnitCostBilledParentLineKeyQty_OrderedQty_Shipped
000613560009311250sales@wayneindustires.com131.6152True07977712.00
000613570009321251contact@starkindustries.com131.6152True085695196.50
000613580009331252animation@acmetoons.com129.8511True0001347272

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:

SalesOrderNumWorkTicketNumCustomer_CodeCustomer_ContactUnitCostBilledParentLineKeyQty_OrderedQty_Shipped
000613560009311250sales@wayneindustires.com131.6152True07977712.00
000613570009321251contact@starkindustries.com131.6152True085695196.50
000613580009331252animation@acmetoons.com129.8511True0001347272

and the Customer_Info table :

NameIndustryCodeAddressCityDiscountPhoneNumberEmail
Wayne EnterprisesDefense,weaponry,aerospace,enginerringNULL1631 Dark Knight WayGotham19.755556614000sales@wayneindustires.com
Stark IndustriesDefense,weaponry,protection12515641 Iron DrUndisclosed19.739993126156contact@starkindustries.com
Acme CorpComedy,laughter,animation125224569 Smiling StToon Town17.533216549877animation@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:

NameIndustryCodeAddressCityDiscountPhoneNumberEmail
Wayne EnterprisesDefense,weaponry,aerospace,enginerring12501631 Dark Knight WayGotham19.755556614000sales@wayneindustires.com
Stark IndustriesDefense,weaponry,protection12515641 Iron DrUndisclosed19.739993126156contact@starkindustries.com
Acme CorpComedy,laughter,animation125224569 Smiling StToon Town17.533216549877animation@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!