SQL changing data

• ‹ previous
• 5 of 6
• next ›

There are three basic data changing SQL statements:

• UPDATE
• DELETE
• INSERT

Some database engines support other additional commands (eg. MySQL - REPLACE)

UPDATE - changing existing data

UPDATE command modifies data in existing rows.

Syntax:

UPDATE table_name SET column1=value1[, column2=value2...] WHERE condition

Examples:

UPDATE Customer SET Country = 'United States' WHERE Country = 'USA'
UPDATE Customer SET FirstName = 'John' WHERE CustomerId = 17

If WHERE clause is omited, UPDATE command modifies all rows in updated table. Be careful, database has not undo operation.

DELETE - removing existing data

DELETE command removes existing rows from table.

Syntax:

DELETE FROM table_name WHERE condition

Examples:

DELETE FROM Customer WHERE CustomerId = 17

When WHERE clause is omited, DELETE command deletes all rows from references table. Be careful, database has not undo operation. There could be some complications with delete command, when deleted row is referenced from other table.

INSERT - add new data

INSERT commands adds new row to existing table.

Syntax:

INSERT INTO table_name (col1, col2, col3) VALUES (value1, value2, value3)

Examples:

INSERT INTO Genre (GenreId, Name) VALUES (26, 'Bluegrass')

INSERT - SELECT

You could skip this paragraph, if you need only basic information. INSERT statement can be used to result of SELECT statement. This is very useful and effective way, how to add more rows in one SQL command.

Syntax:

INSERT INTO table_name (col1, col2, col3) SELECT_statement

SELECT_statement must return the some number of columns as requested in INSERT command.

Example:
Warning - for this example, I will use SQL command I have not described before - CREATE TABLE. It creates new table, which is needed for INSERT-SELECT command. It works only under SQLite database (it uses specific data types and SQLite || string concatening operator)

CREATE TABLE UsaCustomer (CustomerId INTEGER PRIMARY KEY, FullName TEXT);
INSERT INTO UsaCustomer (CustomerId, FullName) SELECT CustomerId, FirstName || ' ' || LastName FROM Customer WHERE Country='USA'

• ‹ previous
• 5 of 6
• next ›