BA371 — SQL — insertion, updates & deletion
- Inserting records (all columns):
- insert into <table_name> values (<list of comma-separated values>);
- insert into project values (1600, 'Q4 Tax Prep', 1, 100);
- Notice that SQL strings are delimited by single quotes. Strings within these quotes are case-sensitive.
- Column list and order can be specified as follows:
- insert into <table_name> (<list of comma-separated column_names>) values (<list of comma-separated values>);
- insert into project (project_name, project_id) values ('Q4 Tax Prep', 1500);
- Some entries require special syntax: e.g.,
- create table foo (my_date date not null);
- SQL*Server: insert into foo values ('3-mar-2019');
- SQL*Server: insert into foo values ('2019-03-18'); ... ASCII data format
- SQL*Server: insert into foo values (getdate());
- Oracle: insert into foo values (SYSDATE);
- MySql: insert into foo values (NOW());
- Null values: insert into project values (1600, 'Q4 Tax Prep', 1, null);
- Data retrieved from elsewhere in the database:
INSERT INTO table_x
SELECT something FROM some_table
- Using inserts to load large amounts of data from a text file can take a looooong time.
- All industry-strength RDBMS's have a so-called direct load facility.
- Specify the read format, the table to load the data to and point the RDMBS to the file containing the data.
- Need rollback/commit.
- delete from project; ... deletes all records from table; accomodates where clause project.
- truncate table project; ... deletes all records and de-allocates space.
- delete from project where project_id > 2000;
- delete from project where project_id in (1010, 1020, 1030, 2000);
- delete from project where project_id in (select * from some_table_x where condition);
- Notice that the where clause qualifies (puts constraints on) the delete.
- update <table_name> set <column_name> = <value> (where <condition>);
- update project set project_name = 'Q2 Tax Preparation' where project_id = 1600;
- An updates can be replaced by a delete followed by an insert.