Home / code / sql

Add and change data

INSERT

With these instructions you can add 1 or more records.

1 record:
INSERT INTO target [(field1,field2,...)] VALUES (value1,value2,...)

Multiple records:
INSERT INTO target [IN externdatabase] [(field1,field2,...)] SELECT [source.]field1, field2, ... FROM table-expression

Part Explanation
target Name of the table or query where the records should be added.
Externdatabase Path to an another database
source Name of the table or query from where the records should be copied.
field1, field2, ... Names of the fields where the data should be added.
table-expression Name of the table(s) from where the records are from.
value1, value2, ... The values that should be added in the fields of the new records. You should add every value between quotes ("") and every value separated by commas(,).

If you don't add a value to a field the standard value is added (NULL). Records are added to the end of the table.

If the target table has a primary-key you must add a unique (not Null) value. If you don't do that, no records are added.

You don't have to write the fields in your instruction, but then you have to add a value for every field.

I you have linked the table with other tables then you can not enter values (in the linked fields) that are not in the other tables.

UPDATE

Here you can change value of existing records, on basis of criteria.

UPDATE table SET newvalue WHERE criteria

Part Explanation
table Name of the table where the records should be changed
newvalue An expression that set the value that should be insert in the records
criteria Expression that decides which records should be updated

Example:
UPDATE place SET address='newstreet 111', postcode=1000 city='new city' WHERE placenr=99
Here address, postcode and city of the person with the placenr 99.

You can not undo a UPDATE-instruction, you might want to test your instruction with SELECT.

Delete

With this instruction you remove records from the table.

DELETE FROM table WHERE criteria

Part Explanation
table Name of the table where the records should be deleted
criteria Expression that decides which records should be deleted

Example:
DELETE FROM biers WHERE biernr=99
Remove the record with biernr 99.

Delete is used to delete records. If you want remove a complete table use drop.
With drop the structure, indexes, data types, are also removed.

You cannot delete records if they are needed in another table.

Delete removes the entire records and not only a few values.

If you want to remove values, use a UPDATE with Null.

Again, you cannot undo a delete. So first test with SELECT.

 

TOP

Latest script:

 

Books: