Sec 5
DATA MANIPULATION LANGUAGE (DML)
Data manipulation language
INSERT
UPDATE
DELETE
INSERT INTO Syntax
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
OR INSERT INTO COLUMN ORDER AS
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
Example
INSERT INTO Customers (CustomerName, ContactName, Address, City,
PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen
21', 'Stavanger', '4006', 'Norway');
or
INSERT INTO Customers
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen
21', 'Stavanger', '4006', 'Norway');
Only in Specified Columns
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
Only in Specified Columns
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
Insert Multiple Rows
INSERT INTO Customers (CustomerName, ContactName, Address, City,
PostalCode, Country)
VALUES
('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'),
('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway'),
('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1 0AA', 'UK');
UPDATE STATEMENT
The UPDATE statement is used to modify the existing records in a table.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Example
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
UPDATE Multiple Records
UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';
Be careful when updating records. If you omit the WHERE clause, ALL records will
be update
UPDATE Customers
SET ContactName='Juan';
DELETE Statement
DELETE FROM table_name WHERE condition;
Example
DELETE FROM Customers
WHERE
CustomerName='Alfreds Futterkiste‘;
Delete All Records
It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and
indexes will be intact:
EX
DELETE FROM Customers;
Delete number of row
ex
Delete top(3) from customers
SQL MERGE Statement
SQL MERGE Statement combines INSERT, DELETE, and UPDATE statements into one single query.
MERGE Statement in SQL
MERGE statement in SQL is used to perform insert, update, and delete operations on a target table based on
the results of JOIN with a source table. This allows users to synchronize two tables by performing operations on
one table based on results from the second table.
The MERGE statement compares data between a source table and a target table based on specified key fields.
It performs appropriate actions like inserting new records, updating existing ones, and deleting .
Syntax
MERGE INTO target_table
USING source_table
ON merge_condition
WHEN MATCHED THEN
UPDATE SET column1 = value1 [, column2 = value2 …]
WHEN NOT MATCHED THEN
INSERT (column1 [, column2 …])
Notes in pervious Example
1) The cost of COFFEE in TARGET is 15.00 while in SOURCE it is 25.00
PRODUCT_LIST
102 COFFEE 15.00
UPDATED_LIST
COFFEE 25.00
2. There is no BISCUIT product in SOURCE but it is in TARGET
PRODUCT_LIST
103 BISCUIT 20.00
3. There is no CHIPS product in TARGET but it is in SOURCE
UPDATED_LIST
104 CHIPS 22.00
Therefore, three operations need to be done in the TARGET according to the above discrepancies. They are :
1. UPDATE operation
102 COFFEE 25.00
2. DELETE operation
103 BISCUIT 20.00
3. INSERT operation
104 CHIPS 22.00
