On 11/21/2011 3:32 PM, Don V Nielsen wrote:
I'm confused by the error message, "near 'order' : syntax error", with the
following statement. The same statement, beginning with "select *" in
place of "delete", will function fine.
delete from seg_ny_adds
where needid = 90
order by prty desc
limit (select count() from seg_ny_adds where needid = 90) - 2
You need to build SQLite with a particular option to enable LIMIT and
ORDER BY clauses in DELETE statement:
http://sqlite.org/compile.html#enable_update_delete_limit
Alternatively, change your statement to
delete from seg_ny_adds where rowid in (
select rowid from seg_ny_adds
where needid = 90
order by prty desc
limit (select count() from seg_ny_adds where needid = 90) - 2
);
or
delete from seg_ny_adds
where needid = 90 and rowid not in (
select rowid from seg_ny_adds
where needid = 90
order by prty limit 2
);
Replace "rowid" with the name of INTEGER PRIMARY KEY column, if you have
one.
What I am trying to accomplish is to remove from each group (needid
identifies the group) all but the top 2 rows.
The plan is to loop through the table, identify each group that has more
than two rows, order them by priority descending,
and then delete the top records leaving the bottom two
You should be able to do the whole thing in a single statement, without
any loop:
delete from seg_ny_adds where rowid not in (
select rowid from seg_ny_adds s
where s.needid = seg_ny_adds.needid
order by prty limit 2
);
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
