Updating Data in Database Tables
In order to update existing data in a database table you can use UPDATE statement. To update data you have to specify at least 3 things:
- Name of table and column where you want to update
- New value of columns
- Which records or rows you want update
Here is the syntax of UPDATE statement:
UPDATE table_name
SET col1 = new_val1,
col2 = new val2
WHERE conditions
Let's look at the UPDATE statement in detail. First is the key word UPDATE followed by the table name where you want to update. Next is the key word SET followed by the column name, an equals operate and the new column value. The new column value can be constant value or any scalar expression. If you want to update more than one column, each pair column and new column value must be separated by a comma. The WHERE clause allows you to restrict which rows should be updated. If the row satisfy the conditions of WHERE clause, It will be updated. There is no error if now row satisfies the condition, in this case no row is updated.
Let's take a look at several examples of updating data by using UPDATE statement.
For example if you want to update quantity column of stock table of all items to 100, you can use the following query:
UPDATE stock
SET quantity = 100
If you want to update subset of rows in the
stock table you can use the WHERE clause to do so. Here is the query to update only item with id 1,2, and 5.
UPDATE stock
SET quantity = 50
WHERE item_id IN (1,2,5)
Sometimes it is very important to use the WHERE clause. If you omitted it, the whole table is updated which may not be as intended.