Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.UPDATE and DELETE | DDL and DML in SQL
Intermediate SQL
course content

Course Content

Intermediate SQL

Intermediate SQL

1. Grouping
GROUP BYOrder of StatementsTotal Ride Time for Each Subway Line ChallengeAverage Student's Grade ChallengeSearching for the Top Math Students ChallengeHAVINGSeveral Aggregate Functions in the QueryMore Complex Filtering
2. Nested Subqueries
Nested QueriesNon-tech Department Employees ChallengeUsing Inner Subqueries in the FROM SectionEmployees With More Than Average Salary ChallengeUNION ClauseCombining Table Results
3. Joining Tables
JOIN 2 TablesGet Category By Product PriceGet Minimum Price By CategoryGet Product Amount By CategoryLEFT, RIGHT and INNER JOINsTable Join PracticeFULL JOIN Usage
4. DDL and DML in SQL
CREATE and ConstraintsALTER and INSERT OperationsDROP and TRUNCATEUPDATE and DELETE

book
UPDATE and DELETE

It's time to get back to writing queries and consider two more statements.

You already know how to clear a table, add a column, insert data, etc. But for proper interaction with the database, we need to understand how to update and delete individual rows.

For this, there are two statements and types of queries: the UPDATE and the DELETE queries.

UPDATE

UPDATE: Used to modify existing data in a table. With such a query, we can change data in a table without affecting other rows. Let's look at an example with the medications table, which looks like this:

Let's imagine we need to update the price for a specific type of medication. For example, currently, there's a 50% discount on ibuprofen, and we need to change the price for this product.

Our update query will look like this:


              
1234567

            
UPDATE medications
SET price = 4
WHERE id = 2;

SELECT * 
FROM medications
ORDER BY id;
copy

Here, we updated the medications table so that the price for the product with id 2 (ibuprofen) will be set to 4. After that, we selected all the columns from the table to ensure the price column was successfully updated. You can substitute any value and see how the update operation works in SQL.

In summary, the general syntax looks like this:

DELETE

The operation of the DELETE statement is almost identical in principle. However, here we do not use the keyword SET because we are not changing anything; we are simply deleting rows.

The syntax for deletion will look like this:

But I'll remind you that deleting rows should be done with caution, as you won't be able to simply recover them.

Note

If you don't include a WHERE condition, the data will be updated or deleted for all rows.

Let's move on to the practice!

Task
test

Swipe to show code editor

In the pharmaceutical market, there's inflation!

The supplier company said we urgently need to set a minimum price for the product - 10 units. So your task will be to update the table so that the price, which was previously less than 10, becomes 10. You can do this using the UPDATE statement.

Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Everything was clear?

How can we improve it?

Thanks for your feedback!

Section 4. Chapter 4
toggle bottom row

book
UPDATE and DELETE

It's time to get back to writing queries and consider two more statements.

You already know how to clear a table, add a column, insert data, etc. But for proper interaction with the database, we need to understand how to update and delete individual rows.

For this, there are two statements and types of queries: the UPDATE and the DELETE queries.

UPDATE

UPDATE: Used to modify existing data in a table. With such a query, we can change data in a table without affecting other rows. Let's look at an example with the medications table, which looks like this:

Let's imagine we need to update the price for a specific type of medication. For example, currently, there's a 50% discount on ibuprofen, and we need to change the price for this product.

Our update query will look like this:


              
1234567

            
UPDATE medications
SET price = 4
WHERE id = 2;

SELECT * 
FROM medications
ORDER BY id;
copy

Here, we updated the medications table so that the price for the product with id 2 (ibuprofen) will be set to 4. After that, we selected all the columns from the table to ensure the price column was successfully updated. You can substitute any value and see how the update operation works in SQL.

In summary, the general syntax looks like this:

DELETE

The operation of the DELETE statement is almost identical in principle. However, here we do not use the keyword SET because we are not changing anything; we are simply deleting rows.

The syntax for deletion will look like this:

But I'll remind you that deleting rows should be done with caution, as you won't be able to simply recover them.

Note

If you don't include a WHERE condition, the data will be updated or deleted for all rows.

Let's move on to the practice!

Task
test

Swipe to show code editor

In the pharmaceutical market, there's inflation!

The supplier company said we urgently need to set a minimum price for the product - 10 units. So your task will be to update the table so that the price, which was previously less than 10, becomes 10. You can do this using the UPDATE statement.

Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Everything was clear?

How can we improve it?

Thanks for your feedback!

Section 4. Chapter 4
Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
We're sorry to hear that something went wrong. What happened?
some-alt