Some additional info on this question. I can get the deletion to work
in SQLite using the syntax below.
DELETE from timeline where timeline.name IN (SELECT distinct
timeline.name FROM timeline,del_timelines where
timeline.name=del_timelines.name and
timeline.last_change<=del_timelines.last_change)
Is this the only way to perform this sort of deletion in SQLite?
Thank you all again.
Shawn M. Downey
MPR Associates
632 Plank Road, Suite 110
Clifton Park, NY 12065
518-371-3983 x3 (work)
860-508-5015 (cell)
-----Original Message-----
From: Downey, Shawn
Sent: Thursday, July 14, 2005 11:57 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] proper SQL DELETE syntax
What is the correct SQL syntax for deleting records from a single table
based on a where clause with multiple table references?
For example:
Sqlite> DELETE from timeline where timeline.name=del_timelines.name and
timeline.last_change<=del_timelines.last_change;
SQL error: no such column: del_timeline.name
This is probably because I did not refer to the del_timelines table in
the from clause. But I only want to delete only from the timelines
table - not the del_timelines table.
MySQL allows me a different syntax for DELETE:
MySQL> DELETE timeline FROM timeline,del_timeline where ....
But this alternate DELETE syntax does not seem to be allowed in sqlite.
Thanks for any help.
Schemas follow:
CREATE TABLE timeline(
name VARCHAR(32) not null,
sim_time VARCHAR(10),
perf_time VARCHAR(10),
plot_time VARCHAR(10) ,
dmg_code VARCHAR(32) not null,
dmg_type VARCHAR(1) not null,
compt_id VARCHAR(16),
item_id VARCHAR(16),
neighbor VARCHAR(16),
source VARCHAR(10),
last_change INTEGER
);
CREATE TABLE del_timelines(
name VARCHAR(32) primary key not null,
last_change INTEGER
);
Shawn M. Downey
MPR Associates
632 Plank Road, Suite 110
Clifton Park, NY 12065
518-371-3983 x3 (work)
860-508-5015 (cell)
