![]() |
Recipe #7: |
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'); |
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'); |
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 |
INSERT INTO test_geom VALUES (2, 'POINT #2', 2.2, GeomFromText('POINT(2.22 3.33)', 4326)); |
INSERT OR IGNORE INTO test_geom VALUES (2, 'POINT #2', 2.2, GeomFromText('POINT(2.22 3.33)', 4326)); |
INSERT OR REPLACE INTO test_geom VALUES (2, 'POINT #2', 2.2, GeomFromText('POINT(2.22 3.33)', 4326)); |
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)); |
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 |
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; |
DELETE FROM test_geom WHERE (id % 2) = 0; |
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 |
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. |