Microsoft.NET

……………………………………………….Expertise in .NET Technologies

Data Manipulation Language (DML) – Part XI

Posted by Ravi Varma Thumati on October 15, 2009

Data Manipulation commands are most widely used SQL commands and they are

  • Insert
  • Update
  • Delete

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);

Example #1 – Simple example

Let’s take a look at a very simple example.

INSERT INTO suppliers (supplier_id, supplier_name)

VALUES (24553, ‘IBM’);

This would result in one record being inserted into the suppliers 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 suppliers
(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’;

UPDATE Statement

The UPDATE statement allows you to update a single record or multiple records in a table.

The syntax for the UPDATE statement is:

UPDATE table
SET column = expression
WHERE predicates;

Example #1 – 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 #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 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.

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

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;

Example #1 – Simple example

Let’s take a look at a simple example:

DELETE FROM suppliers
WHERE supplier_name = ‘IBM’;

This would delete all records from the suppliers 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 suppliers
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 suppliers
WHERE EXISTS
( select customers.name
from customers
where customers.customer_id = suppliers.supplier_id
and customers.customer_name = ‘IBM’ );

This would delete all records in the suppliers table where there is a record in the customers table whose name is IBM, and the customer_id is the same as the supplier_id.

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 suppliers
WHERE EXISTS
( select customers.name
from customers
where customers.customer_id = suppliers.supplier_id
and customers.customer_name = ‘IBM’ );

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: