Course Content
Intermediate SQL
Intermediate SQL
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:
UPDATE medications SET price = 4 WHERE id = 2; SELECT * FROM medications ORDER BY id;
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
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.
Thanks for your feedback!
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:
UPDATE medications SET price = 4 WHERE id = 2; SELECT * FROM medications ORDER BY id;
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
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.
Thanks for your feedback!
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:
UPDATE medications SET price = 4 WHERE id = 2; SELECT * FROM medications ORDER BY id;
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
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.
Thanks for your feedback!
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:
UPDATE medications SET price = 4 WHERE id = 2; SELECT * FROM medications ORDER BY id;
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
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.