Re: [sqlite] delete with an "exists" clause

[Dave Dyer]

>I realize your example may not reflect the specific needs of your 
>application, ...

Yea, my actual application required a query too complex to be
put in an ordinary "where" clause.   I think I've figured it
out - think of the "exists" clause as sort of an implied
join with the table to be partially deleted.

if the selection query is similar to:

  select * from image left join attribute on image.uid=attribute.value

I suppose if SQL had anything like a consistent syntax, I would
expect to write this as something like:

  delete from image left join attribute on image.uid=attribute.value

SQL doesn't allow this, but this "exists" clause is what you can use instead.

  delete from image where exists 
(select * from attribute on attribute.value = image.uid)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] delete with an "exists" clause

[cmartin]

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] delete with an "exists" clause

[Jim Wilcoxson]

You want:

delete from dummy where var=2;

In years of DB work, I've never used exists.  If you're mentioning
this as a bug, I guess it could be: I'd have to lookup exists to see
exactly how it's supposed to work.

Jim

On 4/9/09, 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');
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Software first.  Software lasts!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] delete with an "exists" clause

[Dave Dyer]

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');

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users