Once there's data in the table, we might find that there is a need to modify the data. To do so, we can use the UPDATE command.
The syntax for this is
UPDATE "table_name"
SET "column_1" = [new value]
WHERE {condition}
SET "column_1" = [new value]
WHERE {condition}
Example (1)
say we currently have a table as below:
Table Store_Information
store_name | Sales | Date |
Los Angeles | $1500 | Jan-05-1999 |
San Diego | $250 | Jan-07-1999 |
Los Angeles | $300 | Jan-08-1999 |
Boston | $700 | Jan-08-1999 |
and we notice that the sales for Los Angeles on 01/08/1999 is actually $500 instead of $300, and that particular entry needs to be updated. To do so, we use the following SQL query:
UPDATE Store_Information
SET Sales = 500
WHERE store_name = "Los Angeles"
AND Date = "Jan-08-1999"
SET Sales = 500
WHERE store_name = "Los Angeles"
AND Date = "Jan-08-1999"
The resulting table would look like
Table Store_Information
store_name | Sales | Date |
Los Angeles | $1500 | Jan-05-1999 |
San Diego | $250 | Jan-07-1999 |
Los Angeles | $500 | Jan-08-1999 |
Boston | $700 | Jan-08-1999 |
In this case, there is only one row that satisfies the condition in the WHERE clause. If there are multiple rows that satisfy the condition, all of them will be modified. If no WHERE clause is specified, all rows will be modified.
It is also possible to UPDATE multiple columns at the same time. The syntax in this case would look like the following:
UPDATE "table_name"SET column_1 = [value1], column_2 = [value2]
WHERE {condition}
Example #2 - Simple example:
Let's take a look at a very simple example.
UPDATE suppliers
SET name = 'HP'
WHERE name = 'IBM';
This statement would update all supplier names in the suppliers table from IBM to HP.
Example #3 - Updating multiple columns example:
Let's take a look at an example where you might want to update more than one column with a single UPDATE statement.
UPDATE suppliers
SET name = 'Apple', product = 'iPhone'
WHERE name = 'Rim';
When you wish to update multiple columns, you can do this by separating the column/value pairs with commas.
This statement would update the supplier name to "Apple" and product to "iPhone" where the name of the supplier is "Rim".
Example #4 - More complex example:
You can also perform more complicated updates.
You may wish to update records in one table based on values in another table. Since you can't list more than one table in the UPDATE statement, you can use the EXISTS clause.
For example:
UPDATE suppliers SET supplier_name = ( SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id)WHERE EXISTS
( SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id);
Whenever a supplier_id matched a customer_id value, the supplier_name would be overwritten to the customer name from the customers table.
Learn more about the EXISTS condition.
Practice Exercise #1:
Based on the suppliers table populated with the following data, update the city to "Santa Clara" for all records whose supplier_name is "NVIDIA".
CREATE TABLE suppliers ( supplier_id number(10) not null, supplier_name varchar2(50) not null, city varchar2(50), CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id) );
INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5001, 'Microsoft', 'New York');
INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5002, 'IBM', 'Chicago');
INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5003, 'Red Hat', 'Detroit');
INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5004, 'NVIDIA', 'New York');
Solution:
The following SQL statement would perform this update.
UPDATE suppliers
SET city = 'Santa Clara'
WHERE supplier_name = 'NVIDIA';
The suppliers table would now look like this:
SUPPLIER_ID SUPPLIER_NAME CITY 5001 Microsoft New York 5002 IBM Chicago 5003 Red Hat Detroit 5004 NVIDIA Santa Clara
Practice Exercise #2:
Based on the suppliers and customers table populated with the following data, update the city in the suppliers table with the city in the customers table when the supplier_name in the suppliers table matches the customer_name in the customers table.
CREATE TABLE suppliers ( supplier_id number(10) not null, supplier_name varchar2(50) not null, city varchar2(50), CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id) );
INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5001, 'Microsoft', 'New York');
INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5002, 'IBM', 'Chicago');
INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5003, 'Red Hat', 'Detroit');
INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5005, 'NVIDIA', 'LA');
CREATE TABLE customers ( customer_id number(10) not null, customer_name varchar2(50) not null, city varchar2(50), CONSTRAINT customers_pk PRIMARY KEY (customer_id) );
INSERT INTO customers (customer_id, customer_name, city)
VALUES (7001, 'Microsoft', 'San Francisco');
INSERT INTO customers (customer_id, customer_name, city)
VALUES (7002, 'IBM', 'Toronto');
INSERT INTO customers (customer_id, customer_name, city)
VALUES (7003, 'Red Hat', 'Newark');
Solution:
The following SQL statement would perform this update.
UPDATE suppliers SET city = ( SELECT customers.city
FROM customers
WHERE customers.customer_name = suppliers.supplier_name)WHERE EXISTS
( SELECT customers.city
FROM customers
WHERE customers.customer_name = suppliers.supplier_name);
The suppliers table would now look like this:
SUPPLIER_ID SUPPLIER_NAME CITY 5001 Microsoft San Francisco 5002 IBM Toronto 5003 Red Hat Newark 5004 NVIDIA LA
0 Comments