On Thu, 9 Apr 2009, Dave Dyer wrote:
> This little program deletes all rows. Is this a bug, or
> perhaps I misunderstand how delete with an exists clause
> is supposed to work.
>
> drop table if exists dummy;
> create table dummy ( var int);
> insert into dummy (var) values (1);
> insert into dummy (var) values (2);
> insert into dummy (var) values (3);
> delete from dummy where exists (select * from dummy where var='2');
First, you don't need/want the quotes around the 2 since you defined it
as an integer. But that is unrelated to your question.
'exists' returns true if the subquery returns any data at all. Since there
is at least one row where var=2, exists is true, which means it is true
for every row in your main clause ('delete from dummy'), so all rows get
deleted. Your delete query is the functional eqivalent of this:
delete from dummy where 1=1;
Since 1=1 is always true, all rows get deleted.
Note that this qry will not delete any rows:
delete from dummy where exists (select * from dummy where var='999');
because there is no row where var=999. Exists = false for all rows,
nothing is deleted.
I realize your example may not reflect the specific needs of your
application, but in the context of your example, this will achieve what
you want:
delete from dummy where var=2;
Chris
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
