Data Modification Statements

SQL Basics

Search…

SQL Basics

Introduction

Basic Syntax

Basic Select Statements

Table Variables & Set Operators

Subqueries in the Where Clause

Subqueries in the From & Select Clause

The 'Join' Family Operators

Aggregation

Data Modification Statements

Insert Commands

Delete Commands

Update Commands

Data Modification Statements

Inserting Data
In SQL, there are statements for inserting data, deleting data, and modifying data in a database. For inserting data, there are two methods:
INSERT INTO <table>
    VALUES(A1, A2, . . ., An);
The first method allows us to insert one tuple into the database by specifying its actual value. The command above is saying to INSERT INTO a table, then specify the value of that tuple, and the result of the command will be to insert one new tuple into that table with the specified value.
The other method of inserting data into a table is to run a query over the database as a select statement. That select statement will produce a set of tuples, and as long as that set of tuples has the same schema as the table, we could insert all of the tuples into the table.
Like this:
INSERT INTO <table>
    SELECT STATEMENT;
Deleting Data
Deleting data is fairly simple, and looks like this:
DELETE FROM <table>
WHERE <condition>;
The above query is saying to DELETE FROM a table, WHERE a certain condition is true, so this condition is similar to the conditions that we see in the select statement. Then every tuple in the table that satisfies the given condition will be deleted.
This condition can sometimes get fairly complicated, because it can include subqueries, and aggregation over other tables.
Updating Data
Updating data is done through a command similar to the DELETE FROM command. It similarily operates on a single table, it then evaluates a condition over each tuple of the table, and when the condition is true, it will modify the tuple.
It looks like this:
UPDATE <table>
SET <attr> = <expression>
WHERE <condition>;
The above query takes an attribute that is specified and reassigns it to have the value that is the result of the expression. The condition can also get fairly complicated, for it can have subqueries and so on. As well as the expression, for it can involve queries over other tables or the same table in the database.
You can also update multiple attributes in a tuple. You can update any number of attributes simultaneously by evaluating an expression for each, and assigning the result of that expression to the attribute.
Like so:
UPDATE <table>
SET A1=<expr>, A2=<expr>, ...,  An=<expr>
WHERE <condition>;

Aggregation Clauses

Insert Commands

Last modified 2yr ago

Copy link

Outline

Inserting Data

Deleting Data

Updating Data