Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
MERGE - UPSERT operation (insert or update)
CREATE TABLE prod_details
(
prod_id NUMBER(4) ,
prod_name VARCHAR2(30) ,
order_dt DATE DEFAULT SYSDATE ,
Deliver_dt DATE DEFAULT SYSDATE+3 ,
comments VARCHAR2(300)
);
SELECT * FROM prod_details;
no_data_found
INSERT
INSERT INTO prod_details(prod_id,prod_name,order_dt,deliver_dt,comments)
VALUES(100,'Apple iphone 5s','21-May-14','24-May-14','Color : Black');
SELECT * FROM prod_details;
---------------------------------------------------------------------------
PROD_ID PROD_NAME ORDER_DT DELIVER_DT COMMENTS
---------------------------------------------------------------------------
100 Apple iphone 5s 5/21/2014 5/24/2014 Color : Black
---------------------------------------------------------------------------
--Inserting records with out mentioning column name
INSERT INTO prod_details
VALUES(101,'Samsung Galaxy III','20-Aug-14','23-Aug-14','Color : White');
SELECT * FROM prod_details;
---------------------------------------------------------------------------
PROD_ID PROD_NAME ORDER_DT DELIVER_DT COMMENTS
---------------------------------------------------------------------------
100 Apple iphone 5s 5/21/2014 5/24/2014 Color : Black
101 Samsung Galaxy III 8/20/2014 8/23/2014 Color : White
---------------------------------------------------------------------------
--Inserting selective number of values
INSERT INTO prod_details
VALUES(103,'Moto X','11-May-14','13-May-14');
ORA-00947 : not enough values
--While inserting selective number of values mentioning column name is compulsory.
INSERT INTO prod_details (prod_id,prod_name,order_dt,deliver_dt)
VALUES(103,'Moto X','11-May-14','13-May-14');
SELECT * FROM prod_details;
--------------------------------------------------------------------------------
PROD_ID PROD_NAME ORDER_DT DELIVER_DT COMMENTS
--------------------------------------------------------------------------------
100 Apple iphone 5s 5/21/2014 5/24/2014 Color : Black
101 Samsung Galaxy III 8/20/2014 8/23/2014 Color : White
103 Moto X 5/11/2014 5/24/2014
--------------------------------------------------------------------------------
--Inserting NULL value.
--If you want to insert NULL value you can ignore that column at the time of inserting
--or we can use NULL keyword to insert NULL.
INSERT INTO prod_details
VALUES(104,'Moto G','19-May-14','22-May-14',NULL);
SELECT * FROM prod_details;
---------------------------------------------------------------------------
PROD_ID PROD_NAME ORDER_DT DELIVER_DT COMMENTS
---------------------------------------------------------------------------
100 Apple iphone 5s 5/21/2014 5/24/2014 Color : Black
101 Samsung Galaxy III 8/20/2014 8/23/2014 Color : White
103 Moto X 5/11/2014 5/24/2014
104 Noto G 5/19/2014 5/22/2014
---------------------------------------------------------------------------
--if you are not providing values for order_dt and deliver_dt column default value can be taken.
INSERT INTO prod_details(prod_id,prod_name,comments)
VALUES(105,'Nokia Lumis 720p','Color : Red');
SELECT * FROM prod_details;
---------------------------------------------------------------------------
PROD_ID PROD_NAME ORDER_DT DELIVER_DT COMMENTS
----------------------------------------------------------------------------
100 Apple iphone 5s 5/21/2014 5/24/2014 Color : Black
101 Samsung Galaxy III 8/20/2014 8/23/2014 Color : White
103 Moto X 5/11/2014 5/24/2014
104 Moto G 5/19/2014 5/22/2014
105 Nokia Lumis 720p 5/26/2014 5/29/2014 Color : Red
---------------------------------------------------------------------------
--Inserting data by using sub query
CREATE TABLE test_tab (id NUMBER, Name VARCHAR2(30));
INSERT INTO test_tab VALUES(1,'Name1');
INSERT INTO test_tab VALUES(2,'Name2');
INSERT INTO test_tab VALUES(3,'Name3');
SELECT COUNT(*) FROM test_tab;
COUNT(*)
-------
3
--creating table by using sub query (with out data)
CREATE TABLE ins_chk
SELECT * FROM test_tab
WHERE id = 900;
SELECT COUNT(*) FROM ins_chk;
COUNT(*)
-------
0
--Inserting data by using sub query
--copying data from test_tab to ins_chk
INSERT INTO ins_chk (SELECT * FROM test_tab);
3 rows inserted in 0.047 seconds.
SELECT COUNT(*) FROM ins_chk;
COUNT(*)
-------
3
DROP TABLE test_tab;
DROP TABLE ins_chk;
UPDATE
Syntax :
UPDATE table_name
SET column1_name = column1_value,
column2_name = column2_value,
column2_name = column3_value,
columnn_name = columnn_value
WHERE condition(s);
SELECT * FROM prod_details;
---------------------------------------------------------------------------
PROD_ID PROD_NAME ORDER_DT DELIVER_DT COMMENTS
----------------------------------------------------------------------
100 Apple iphone 5s 5/21/2014 5/24/2014 Color : Black
101 Samsung Galaxy III 8/20/2014 8/23/2014 Color : White
103 Moto X 5/11/2014 5/24/2014
104 Moto G 5/19/2014 5/22/2014
105 Nokia Lumis 720p 5/26/2014 5/29/2014 Color : Red
---------------------------------------------------------------------------
UPDATE prod_details ps
SET ps.prod_name = 'iphone 5s'
WHERE ps.prod_id = 100;
1 row updated in 0.031 seconds
SELECT *
FROM prod_details ps
WHERE ps.prod_id = 100;
--------------------------------------------------------------------
PROD_ID PROD_NAME ORDER_DT DELIVER_DT COMMENTS
--------------------------------------------------------------------
100 iphone 5s 5/21/2014 5/24/2014 Color : Black
--------------------------------------------------------------------
--update statement with out condition
--If you try to execute update statement without condition it'll update all the records inside the table.
UPDATE prod_details ps
SET ps.comments = 'None';
5 row updated in 0.031 seconds
SELECT *
FROM prod_details ps;
---------------------------------------------------------------------------
PROD_ID PROD_NAME ORDER_DT DELIVER_DT COMMENTS
---------------------------------------------------------------------------
100 Apple iphone 5s 5/21/2014 5/24/2014 None
101 Samsung Galaxy III 8/20/2014 8/23/2014 None
103 Moto X 5/11/2014 5/24/2014 None
104 Moto G 5/19/2014 5/22/2014 None
105 Nokia Lumis 720p 5/26/2014 5/29/2014 None
----------------------------------------------------------------------
--if your update text contain ' means you can use following metnod (use '')
UPDATE prod_details ps
SET ps.comments = 'Some product''s are not available'
WHERE ps.prod_id = 100;
1 row updated in 0.031 seconds
SELECT *
FROM prod_details ps
WHERE ps.prod_id = 100;
------------------------------------------------------------------------------------
PROD_ID PROD_NAME ORDER_DT DELIVER_DT COMMENTS
------------------------------------------------------------------------------------
100 iphone 5s 5/21/2014 5/24/2014 Some product's are not available
------------------------------------------------------------------------------------
DELETE
Syntax:
DELETE FROM table_name
WHERE condition(s);
DELETE FROM prod_details
WHERE prod_id IN (104, 105);
2 row(S) deleted in 0.032 seconds
SELECT *
FROM prod_details ps;
---------------------------------------------------------------------------------------
PROD_ID PROD_NAME ORDER_DT DELIVER_DT COMMENTS
---------------------------------------------------------------------------------------
100 Apple iphone 5s 5/21/2014 5/24/2014 Some product's are not available
101 Samsung Galaxy III 8/20/2014 8/23/2014 None
103 Moto X 5/11/2014 5/24/2014 None
---------------------------------------------------------------------------------------
DELETE FROM prod_details;
3 row(s) deleted in 0.062 seconds.
SELECT * FROM prod_details;
no rows selected.
DROP TABLE prod_details;
MERGE = Insert + Update
-- will update soon.
Sir, Need explanation for MERGE statement with example
ReplyDelete