DML STATEMENTS(INSERT, UPDATE, DELETE)

INSERT

INSERT INTO table1 (column1, column2)
VALUES (column1_value, 'column2_value')

characters and dates need ‘single quote’

If you are inserting all values from the table then you can just write

INSERT INTO table1 VALUES (first_column_value, last_column_value);

Here is an interesting usage of INSERT, pay attention that there is no word VALUES stated

INSERT INTO table1 (column1, column2)
SELECT column1, column2 FROM table2
WHERE some_condition = some_value

UPDATE

Updates existing column values. In this case, column1 will change to new_value from old_value.

UPDATE table1
SET column1 = 'new_value'
WHERE column1 = 'old_value'

If we do not put WHERE clause it will change all the rows in the table and not just specific ones.

UPDATE table1
SET column1 = 'new_value'

We can also update more columns at once

UPDATE table1
SET column1 = 1,
column2 = 2
WHERE column1 = 3

DELETE

Deleting should not be confused with TRUNCATE. DELETE just deletes the values of rows and nothing else.
To remove rows from a table, there are two options: the DELETE command and the
TRUNCATE command. DELETE is less drastic, in that a deletion can be rolled
back whereas a truncation cannot be. DELETE is also more controllable, in that it
is possible to choose which rows to delete, whereas a truncation always affects the
whole table. DELETE is, however, a lot slower and can place a lot of strain on the
database. TRUNCATE is virtually instantaneous and effortless. TRUNCATE completely
empties the table. There is no concept of row selection, as there is with a DELETE.

If we want to delete all rows from table

DELETE FROM table_name

If we want to delete specific rows we use WHERE

DELETE FROM table_name
WHERE some_column='some_value'

When deleting rows from the table we should pay special attention to referential integrity between foreign keys and reference tables!

For all DML functions to take effect we need to COMMIT them, either by writing the command after the code or it will be done automatically when the connections is closed or program is properly exited.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: