The UPDATE statement allows you to update a single record or multiple records in a table.
The syntax the UPDATE statement is:
UPDATE table
SET column = expression
WHERE predicates;
SET column = expression
WHERE predicates;
Example #1 - Simple example
Let's take a look at a very simple example.
UPDATE supplier
SET name = 'HP'
WHERE name = 'IBM';
SET name = 'HP'
WHERE name = 'IBM';
This statement would update all supplier names in the supplier table from IBM to HP.
Example #2 - 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 supplier
SET supplier_name =
( SELECT customer.name
FROM customers
WHERE customers.customer_id = supplier.supplier_id)
FROM customers
WHERE customers.customer_id = supplier.supplier_id)
WHERE EXISTS
( SELECT customer.name
FROM customers
WHERE customers.customer_id = supplier.supplier_id);
( SELECT customer.name
FROM customers
WHERE customers.customer_id = supplier.supplier_id);
Whenever a supplier_id matched a customer_id value, the supplier_name would be overwritten
INSERT Statement
The INSERT statement allows you to insert a single record or multiple records into a table.
The syntax for the INSERT statement is:
INSERT INTO table
(column-1, column-2, ... column-n)
VALUES
(value-1, value-2, ... value-n);
(column-1, column-2, ... column-n)
VALUES
(value-1, value-2, ... value-n);
Example #1 - Simple example
Let's take a look at a very simple example.
INSERT INTO supplier
(supplier_id, supplier_name)
VALUES
(24553, 'IBM');
This would result in one record being inserted into the supplier table. This new record would have a supplier_id of 24553 and a supplier_name of IBM.
Example #2 - More complex example
You can also perform more complicated inserts using sub-selects.
For example:
INSERT INTO supplier
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city = 'Newark';
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city = 'Newark';
By placing a "select" in the insert statement, you can perform multiples inserts quickly.
With this type of insert, you may wish to check for the number of rows being inserted. You can determine the number of rows that will be inserted by running the following SQL statement before performing the insert.
SELECT count(*)
FROM customers
WHERE city = 'Newark';
Frequently Asked Questions
Question: I am setting up a database with clients. I know that you use the "insert" statement to insert information in the database, but how do I make sure that I do not enter the same client information again?
Answer: You can make sure that you do not insert duplicate information by using the EXISTS condition.
For example, if you had a table named clients with a primary key of client_id, you could use the following statement:
INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE not exists (select * from clients
where clients.client_id = suppliers.supplier_id);
FROM suppliers
WHERE not exists (select * from clients
where clients.client_id = suppliers.supplier_id);
This statement inserts multiple records with a subselect.
If you wanted to insert a single record, you could use the following statement:
INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 10345);
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 10345);
The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.
DELETE Statement
The DELETE statement allows you to delete a single record or multiple records from a table.
The syntax for the DELETE statement is:
DELETE FROM table
WHERE predicates;
WHERE predicates;
Example #1 - Simple example
Let's take a look at a simple example:
DELETE FROM supplier
WHERE supplier_name = 'IBM';
WHERE supplier_name = 'IBM';
This would delete all records from the supplier table where the supplier_name is IBM.
You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by running the following SQL statement before performing the delete.
SELECT count(*)
FROM supplier
WHERE supplier_name = 'IBM';
FROM supplier
WHERE supplier_name = 'IBM';
Example #2 - More complex example
You can also perform more complicated deletes.
You may wish to delete records in one table based on values in another table. Since you can't list more than one table in the FROM clause when you are performing a delete, you can use the EXISTS clause.
For example:
DELETE FROM supplier
WHERE EXISTS
( select customer.name
from customer
where customer.customer_id = supplier.supplier_id
and customer.customer_name = 'IBM' );
This would delete all records in the supplier table where there is a record in the customer table whose name is IBM, and the customer_id is the same as the supplier_id.
Learn more about the EXISTS condition.
If you wish to determine the number of rows that will be deleted, you can run the following SQL statement before performing the delete.
SELECT count(*) FROM supplier
WHERE EXISTS
( select customer.name
from customer
where customer.customer_id = supplier.supplier_id
and customer.customer_name = 'IBM' );
Frequently Asked Questions
Question: How would I write an SQL statement to delete all records in TableA whose data in field1 & field2 DO NOT match the data in fieldx & fieldz of TableB?
Answer: You could try something like this:
DELETE FROM TableA
WHERE NOT EXISTS
( select *
from TableB
where TableA .field1 = TableB.fieldx
and TableA .field2 = TableB.fieldz );
WHERE NOT EXISTS
( select *
from TableB
where TableA .field1 = TableB.fieldx
and TableA .field2 = TableB.fieldz );
