SQL Data Manipulation Statement: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
m (Text replacement - ". ----" to ". ----") |
||
(12 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
A [[SQL Data Manipulation Statement]] is a [[Data Manipulation Statement]] within a [[SQL language]]. | A [[SQL Data Manipulation Statement]] is a [[Data Manipulation Statement]] that is a [[SQL statement]] (within a [[SQL language]]). | ||
* <B>Context:</B> | * <B>Context:</B> | ||
** It can range from being a [[SQL INSERT Statement]], to being a [[SQL UPDATE Statement]] to being a [[SQL DELETE Statement]]. | ** 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]]. | ||
* <B | * <B>Example(s):</B> | ||
** <code>SELECT * INTO ... </code> | ** <code>SELECT * INTO ... </code> | ||
** a [[MySQL Data Manipulation Statement]], such as: | ** a [[MySQL Data Manipulation Statement]], such as: | ||
Line 12: | Line 12: | ||
** a [[SQL Data Querying Statement]]. | ** a [[SQL Data Querying Statement]]. | ||
** a [[Hash Data Manipulation Statement]]. | ** a [[Hash Data Manipulation Statement]]. | ||
* <B | * <B>See:</B> [[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: | |||
*** <code>[[Insert (SQL)|INSERT]]</code> adds rows (formally [[tuple]]s) to an existing table, e.g.: <source lang="sql"> INSERT INTO example (field1, field2, field3) VALUES ('test', 'N', NULL); </source> | |||
*** <code>[[Update (SQL)|UPDATE]]</code> modifies a set of existing table rows, e.g.: <source lang="sql"> UPDATE example SET field1 = 'updated value' WHERE field2 = 'N'; </source> | |||
*** <code>[[Delete (SQL)|DELETE]]</code> removes existing rows from a table, e.g.: <source lang="sql"> DELETE FROM example WHERE field2 = 'N'; </source> | |||
*** <code>[[Merge (SQL)|MERGE]]</code> is used to combine the data of multiple tables. It combines the <code>INSERT</code> and <code>UPDATE</code> 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> | |||
---- | |||
[[Category:Concept]] | [[Category:Concept]] |
Latest revision as of 20:41, 23 September 2021
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):
- 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 theINSERT
andUPDATE
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>
- The Data Manipulation Language (DML) is the subset of SQL used to add, update and delete data: