How to Update Only the First 10 Rows in SQL

Real-world databases require continuous updating. Often, you need to update specific records; you may even want to update only the first row, or the first 10, 100, or 1000 rows. Let’s explore how to update rows for a specified quantity from the top in SQL.

What Is the SQL UPDATE?

In SQL, an UPDATE statement modifies existing records of a table. You may choose to update entire columns or update rows filtered with a WHERE statement. As we’ll see in this article, you can also update rows for a specified quantity from the top of your table.

Keeping databases up to date is one of the key responsibilities of data engineers, data administrators, and everyone working with databases. Check out this detailed guide for explanations and beginner-friendly examples of SQL UPDATE.

To learn all the basics of modifying databases, check out our interactive course How to INSERT, UPDATE, and DELETE Data in SQL. With 52 interactive SQL exercises, you learn how to retrieve, store, modify, delete, insert, and update data with the SQL data manipulation language (DML). If you are very new to SQL, I recommend taking the SQL Basics interactive course before moving to data manipulation language.

How to Update Just One Row in SQL

The best way to understand how the SQL UPDATE works is to see it in action. So, let’s start with an example.

We have a table of our suppliers, with the company ID, company name, contact person name, and volume of products and services provided in 2021 (in USD).

Let’s keep it simple and start with updating just one row of the table. Specifically, let’s say we’ve just learned the contact person for “Lots of Fun” has changed from Phyllis Vance to Pam Beesly. We want to update the contact name for the company with ID 9. Here’s a query we can use:

UPDATE suppliers
SET contact_name = 'Pam Beesly'
WHERE id = 9;

The syntax is straightforward. After the UPDATE keyword, we specify the name of the table we want to update. Then, with the SET keyword, we define which column (i.e., contact_name) is to be updated with what value (i.e., Pam Beesley). Finally, we use the WHERE keyword to specify that only the record with the company ID 9 is to be updated.

Here’s the updated table. As you see, we now have a new contact person for “Lots of Fun”:

Now, let’s move on to a more complex case in which we update multiple rows.

How to Update a Specific Number of Rows

There are many different scenarios in which you need to update multiple rows. Most often, you select the rows you want to update using filtering conditions in a WHERE statement.

Here, however, we focus on the case in which you want to update rows for a specified quantity from the top, for example, the first 10 records.

We continue with our first example but with some updates. Let’s say we want to add another column, top_supplier, to our suppliers table. In this column, we want to see:

• “Yes” if the supplier is one of the top 10 by the amount we paid the company last year, or
• “No” for all the other suppliers.

I suggest doing this in three steps:

1. Add a new column top_supplier.
2. Set the value of this column to “No” for all the records.
3. Update the value of this column to “Yes” for the top 10 suppliers.

Here’s how we can accomplish the first two steps:

ALTER TABLE suppliers
ADD top_supplier varchar(32);

UPDATE suppliers
SET top_supplier = 'No';

And here is the resulting table:

Now comes the most interesting part: updating the top_supplier column for the first 10 rows after we order the table by the volume_2021 column. The syntax of the query to accomplish this third step depends on the database management system (e.g., MySQL, SQL Server, Oracle, or PostgreSQL). For now, I’ll show you working queries for a few of the most popular database management systems.

MySQL

In MySQL, we can leverage the LIMIT keyword that limits the number of records to output or to update:

UPDATE suppliers
SET top_supplier = 'Yes'
ORDER BY volume_2021 DESC
LIMIT 10;

With this query, we update the suppliers table by setting the value of the top_supplier column to “Yes”. However, we want these updates for the top 10 suppliers only. So, we order the table based on the volume_2021 column (in descending order), and then limit the number of rows to be updated to 10. If you need to refresh your knowledge of ORDER BY, check out this detailed article.

Here’s the updated table ordered by volume_2021. As you see, only the top 10 suppliers have “Yes” in the last column. Use the same syntax to update only the first row, or the first 5, 20, 100, or any number of rows by simply changing the number after the LIMIT keyword.

SQL Server

Unlike MySQL, SQL Server does not have the LIMIT keyword. Instead, it has TOP. Use this keyword in a subquery to get the IDs of the top 10 suppliers:

UPDATE suppliers
SET top_supplier = 'Yes'
WHERE id IN(
        SELECT TOP (10) id
        FROM suppliers
        ORDER BY volume_2021 DESC);

Then, we update the top_supplier column for the records whose corresponding IDs are found in the list created by the subquery.

PostgreSQL and Oracle

Finally, PostgreSQL and Oracle provide a different way to limit the number of rows to be updated. Similar to the previous query, we use a subquery. With this subquery, we order the suppliers table by the amount paid and then limit the output to 10 records by using the keywords FETCH FIRST 10 ROWS ONLY:

UPDATE suppliers
SET top_supplier = 'Yes'
WHERE id IN(
        SELECT id
        FROM suppliers
        ORDER BY volume_2021 DESC
        FETCH FIRST 10 ROWS ONLY);

As you see, we update only the corresponding records in the main query.

Get more examples of subqueries for the UPDATE statement in this detailed article. Also, see more examples with INSERT, UPDATE, and DELETE here.

Time to Practice With Online SQL Courses!

SQL is a powerful analytical tool that usually outperforms Excel in effectiveness and efficiency. It is also one of the modern programming languages that are here to stay. SQL is not difficult to learn, but often there are several different ways to accomplish the same task. Moreover, there are certain syntax differences across database management systems.

The most effective approach to getting a comprehensive understanding of SQL is to take SQL online courses. If you have no experience with SQL, I recommend starting with the SQL Basics course. It includes 129 interactive SQL exercises to cover all the basics you need for retrieving data from a database.

If you also want to know how to modify your database, check out the course How to INSERT, UPDATE, and DELETE Data in SQL. This interactive course covers the basics of the data manipulation language to help you extend and update your database as needed.

When you are ready to go beyond individual courses and get a comprehensive knowledge of SQL capabilities, go to our learning tracks:

• SQL from A to Z to get comfortable with both basic and advanced SQL concepts such as window functions, recursive queries, and GROUP BY
• Creating Database Structure to learn how to create and manage tables, views, and indexes in popular relational database management systems like MySQL, SQL Server, Oracle, and PostgreSQL.

Thanks for reading, and happy learning!