SQL Data Manipulation Statement

A SQL Data Manipulation Statement is a Data Manipulation Statement that is a SQL statement (within a SQL language).

• Context:
  • It can range from being a SQL INSERT Statement, to being a SQL UPDATE Statement, to being a SQL DELETE Statement, to being a SQL MERGE Statement.
• Example(s):
  • SELECT * INTO ...
  • a MySQL Data Manipulation Statement, such as:
    • LOAD DATA LOCAL INFILE '/path/data.txt' INTO TABLE pet LINES TERMINATED BY '\r\n';
  • a HiveQL Data Manipulation Statement.
  • UPDATE table_one t1 JOIN (SELECT DISTINCT id FROM table2) t2 ON t1.id=t2.id SET t1.flag=1 WHERE pm.value='yes' ;
  • DELETE FROM t1 WHERE id IN (value1, value2, value3, value4, value5);
• Counter-Example(s):
  • a SQL Data Querying Statement.
  • a Hash Data Manipulation Statement.
• See: SQL Data Definition Statement.

---

---

References

2013

• http://en.wikipedia.org/wiki/SQL#Data_manipulation
  • The Data Manipulation Language (DML) is the subset of SQL used to add, update and delete data:
    • INSERT adds rows (formally tuples) to an existing table, e.g.: <source lang="sql"> INSERT INTO example (field1, field2, field3) VALUES ('test', 'N', NULL); </source>
    • UPDATE modifies a set of existing table rows, e.g.: <source lang="sql"> UPDATE example SET field1 = 'updated value' WHERE field2 = 'N'; </source>
    • DELETE removes existing rows from a table, e.g.: <source lang="sql"> DELETE FROM example WHERE field2 = 'N'; </source>
    • MERGE is used to combine the data of multiple tables. It combines the INSERT and UPDATE elements. It is defined in the SQL:2003 standard; prior to that, some databases provided similar functionality via different syntax, sometimes called “upsert”. <source lang="sql"> MERGE INTO table_name USING table_reference ON (condition) WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...] WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ... </source>

---