Note: these pages are no longer maintained

Never the less, much of the information is still relevant.
Beware, however, that some of the command syntax is from older versions, and thus may no longer work as expected.
Also: external links, from external sources, inside these pages may no longer function.

	Recipe #7: Insert, Update and Delete
2011 January 28

Previous Slide

Table of Contents

Next Slide

Since now we've mainly examined how to query tables.
SQL isn't obviously a read-only language: inserting new rows, deleting existing rows and updating values is supported in the most flexible way.

It's now time to examine such topics in deeper detail.

CREATE TABLE test_geom (
  id INTEGER NOT NULL
    PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  measured_value DOUBLE NOT NULL);

SELECT AddGeometryColumn('test_geom', 'the_geom',
4326, 'POINT', 'XY');

Nothing new in this: it's exactly the same table we've already created in the previous example.

INSERT INTO test_geom
    (id, name, measured_value, the_geom)
  VALUES (NULL, 'first point', 1.23456,
    GeomFromText('POINT(1.01 2.02)', 4326));

INSERT INTO test_geom
VALUES (NULL, 'second point', 2.34567,
GeomFromText('POINT(2.02 3.03)', 4326));

INSERT INTO test_geom
    (id, name, measured_value, the_geom)
  VALUES (10, 'tenth point', 10.123456789,
    GeomFromText ('POINT(10.01 10.02)', 4326));

INSERT INTO test_geom
    (the_geom, measured_value, name, id)
  VALUES (GeomFromText('POINT(11.01 11.02)', 4326),
    11.123456789, 'eleventh point', NULL);

INSERT INTO test_geom
(id, measured_value, the_geom, name)
VALUES (NULL, 12.123456789, NULL, 'twelfth point');

The INSERT INTO (...) VALUES (...) statement does exactly what its name states:

the first list enumerates the column names
and the second list contains the values to be inserted: correspondences between columns and values are established by position.
you can simply suppress the column-names list (please, see the second INSERT statement):
anyway this one isn't a good practice, because such statement implicitly relies upon some default column order, and that's not at all a safe assumption.
another interesting point; this table declares a PRIMARY KEY AUTOINCREMENT:
- as a general rule we've passed a corresponding NULL value, so to allow SQLite autogenerating a unique value.
- but in the third INSERT we've set an explicit value (please, check in the following paragraph what really happens in this case).

SELECT *
FROM test_geom;

id	name	measured_value	the_geom
1	first point	1.234560	BLOB sz=60 GEOMETRY
2	second point	2.345670	BLOB sz=60 GEOMETRY
10	tenth point	10.123457	BLOB sz=60 GEOMETRY
11	eleventh point	11.123457	BLOB sz=60 GEOMETRY
12	twelfth point	12.123457	NULL

Just a quick check before going further on ...

INSERT INTO test_geom
VALUES (2, 'POINT #2', 2.2,
GeomFromText('POINT(2.22 3.33)', 4326));

This further INSERT will loudly fail, raising a constraint failed exception.
Accounting for this isn't too much difficult: a PRIMARY KEY always enforces a uniqueness constraint.
And actually one row of id = 2 already exists into this table.

INSERT OR IGNORE INTO test_geom
VALUES (2, 'POINT #2', 2.2,
GeomFromText('POINT(2.22 3.33)', 4326));

By specifying an OR IGNORE clause this statement will now silently fail (same reason as before).

INSERT OR REPLACE INTO test_geom
VALUES (2, 'POINT #2', 2.2,
GeomFromText('POINT(2.22 3.33)', 4326));

There is a further variant: i.e. specifying an OR REPLACE clause this statement will actually act like an UPDATE

REPLACE INTO test_geom
    (id, name, measured_value, the_geom)
  VALUES (3, 'POINT #3', 3.3,
    GeomFromText('POINT(3.33 4.44)', 4326));

REPLACE INTO test_geom
    (id, name, measured_value, the_geom)
  VALUES (11, 'POINT #11', 11.11,
    GeomFromText('POINT(11.33 11.44)', 4326));

And yet another syntactic alternative is supported, i.e. simply using REPLACE INTO:
but this latter simply is an alias for INSERT OR REPLACE.

SELECT *
FROM test_geom;

id	name	measured_value	the_geom
1	first point	1.234560	BLOB sz=60 GEOMETRY
2	POINT #2	2.200000	BLOB sz=60 GEOMETRY
3	POINT #3	3.300000	BLOB sz=60 GEOMETRY
10	tenth point	10.123457	BLOB sz=60 GEOMETRY
11	POINT #11	11.110000	BLOB sz=60 GEOMETRY
12	twelfth point	12.123457	NULL

Just another quick check ...

UPDATE test_geom SET
name = 'point-3',
measured_value = 0.003
WHERE id = 3;

UPDATE test_geom SET
measured_value = measured_value + 1000000.0
WHERE id > 10;

updating values isn't much more complex ...

DELETE FROM test_geom
WHERE (id % 2) = 0;

and the same is for deleting rows.
i.e. this DELETE statement will affect every even id value.

SELECT *
FROM test_geom;

id	name	measured_value	the_geom
1	first point	1.234560	BLOB sz=60 GEOMETRY
3	point-3	0.003000	BLOB sz=60 GEOMETRY
11	POINT #11	1000011.110000	BLOB sz=60 GEOMETRY

A last final quick check ...

Very important notice

Be warned: calling an UPDATE or DELETE statement without specifying any corresponding WHERE clause is a full legal operation in SQL.
Anyway SQL intends that the corresponding change must indiscriminately affect any row within the table: and sometimes this is exactly what you intended to do.

But (much more often) this is a wonderful way allowing to unintentionally destroy or to irreversibly corrupt your data: beginner, pay careful attention.

Previous Slide

Table of Contents

Next Slide

	Author: Alessandro Furieri a.furieri@lqt.it
	This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

	Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.