UPDATE and DELETE Rows from a Table

UPDATE Rows from a Table

The UPDATE statement in SQL is used to modify existing data in a database. It operates on one table at a time, allowing the modification of one or more columns for all rows in a table or selectively specified rows. Let's take a look at an example situation:

In the figure shown above, the my_pokemon table has 4 rows with some NULL values. Now, let's say that you want to update the missing values with an UPDATE statement. You can write an SQL statement as follows:

In the UPDATE statement shown above, we specified the column value to be SYSDATE, where the caught_at column value is missing. Then the corresponding rows are updated with the SYSDATE values. You might omit the WHERE clause depending on your purpose. However, please note that omitting and set a value will affect every row in the table. For example:

In this example, I intently omit the where clause. Then Oracle updated the caught_at column values for all the 4 rows in the table. So, in many practices, you may double-check if you have forgotten any row specification in the WHERE clause.

DELETE Rows from a Table

The DELETE statement is used to remove rows from tables in the database. Just as with the UPDATE statement, rows are identified by the WHERE clause of DELETE; if WHERE is omitted, DELETE removes all the rows from the table. Here's an example of a DELETE statement:

In this example, I deleted two rows whose pokemon_id value equals to 251. The rows that I want to delete from the table is specified in the WHERE clause of the DELETE statement.

Note that when DELETE identifies a row with the WHERE clause, it removes the entire row from the table, not just an individual data element from within a row. If your goal is to remove a single column value from within an existing row and retain the row in the table, do not use DELETE; you can simply use UPDATE statement to set the value to be NULL. For example:

Updating and Deleting Rows under Constraints

Just as with the INSERT statement, the UPDATE and DELETE statement should also comply with any constraints. If the results from UPDATE or DELETE statement violates any constraints on the table, the entire statements will be rejected and none of the modification will be accepted for any of the rows.

For example, let's review the table below:

The table has NOT NULL constraint on the pokemon_id column, meaning that we cannot have any missing value for any of the row for the column. So, the statement below will throw an error:

Similarly, a DELETE statement cannot violate any constraints. This particularly matters when there is a relational constraint between two tables. For example, let's add a foreign key constraint on the pokemon_moves table as follows:

In the statement above, I created a primary key-foreign key relation between the pokemon and the pokemon_moves table, meaning that the column value must be referenced from the id value from the pokemon table. Currently, the id value 1 from the pokemon table is referenced in the pokemon_moves table. In this situation, what if I try to delete a row from the pokemon table whose id equals to 1?

Since there are rows that is currently referencing the target row, we cannot delete the row form the pokemon table.