SpatiaLite logo

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:
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

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

GNU logo 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.