Re: [sqlite] Using a select with 'where'

[Black, Michael (IS)]

You most certainly can do what you want...but your example makes no sense to me.



Sound to me like "personnick" is text and absid is numeric -- they'll never 
match.



You can do something like:

delete from addressbook where absid in (select otherid from grouplinks where 
groupnick='27');



But what you really want is referential integrity.  See this

http://sqlite.org/foreignkeys.html



So when you delete the group the addressbook entries will automagically 
disappear.





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon [turne...@gmail.com]
Sent: Tuesday, May 01, 2012 2:08 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Using a select with 'where'

> >>  delete from addressbook where absid=(select personnick from grouplinks
> where
> >> groupnick='27')
> >>
> >> The 'select personnick ...' can return zero, one, or many results, and
> I'd
> >> like to have the 'delete from ...' delete zero, one, or many rows from
> the
> >> addressbook table. How can I do that with a single statement in SQLite,
> or is
> >> it not possible?
>

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

Re: [sqlite] Using a select with 'where'

[Simon]

> >>  delete from addressbook where absid=(select personnick from grouplinks
> where
> >> groupnick='27')
> >>
> >> The 'select personnick ...' can return zero, one, or many results, and
> I'd
> >> like to have the 'delete from ...' delete zero, one, or many rows from
> the
> >> addressbook table. How can I do that with a single statement in SQLite,
> or is
> >> it not possible?
>


I would normally expect SQLite to give an error (misuse perhaps?) in a
query like this since the right operand to the operator= must evaluate to a
single value.  Otherwise what value is taken? Is "order by" resolved before
taking the first value?  I'd resolve the ambiguity by an error rather than
silently correcting the user's faults.

Just my opinion, but please let me know if a different opinion favored the
current behavior (i like to sync myself with this community!).


In OracleSQL I get this kind of error:
  ORA-01427: single-row subquery returns more than one row


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

Re: [sqlite] Using a select with 'where'

[Tim Streater]

On 27 Apr 2012 at 22:16, Simon Slavin  wrote: 

> On 27 Apr 2012, at 9:00pm, Tim Streater  wrote:

>>  delete from addressbook where absid=(select personnick from grouplinks where
>> groupnick='27')
>>
>> The 'select personnick ...' can return zero, one, or many results, and I'd
>> like to have the 'delete from ...' delete zero, one, or many rows from the
>> addressbook table. How can I do that with a single statement in SQLite, or is
>> it not possible?
>
> The sub-SELECT evaluates to a list, not an individual number.  And 'absid'
> will never equal a list.  You probably mean
>
> DELETE FROM addressbook WHERE absid IN (SELECT personnick FROM grouplinks
> WHERE groupnick = '27')
>
> or something like that.  The syntax tree for DELETE can be found here:
>
> 

Simon,

Yes. I confess to being a mere hacker where SQL itself is concerned. I should 
really have looked up the syntax for expr:





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

Re: [sqlite] Using a select with 'where'

[Simon Slavin]

On 27 Apr 2012, at 9:00pm, Tim Streater  wrote:

> 
>  delete from addressbook where absid=(select personnick from grouplinks where 
> groupnick='27')
> 
> The 'select personnick ...' can return zero, one, or many results, and I'd 
> like to have the 'delete from ...' delete zero, one, or many rows from the 
> addressbook table. How can I do that with a single statement in SQLite, or is 
> it not possible?

The sub-SELECT evaluates to a list, not an individual number.  And 'absid' will 
never equal a list.  You probably mean

DELETE FROM addressbook WHERE absid IN (SELECT personnick FROM grouplinks WHERE 
groupnick = '27')

or something like that.  The syntax tree for DELETE can be found here:



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

Re: [sqlite] Using a select with 'where'

[Tim Streater]

On 27 Apr 2012 at 21:03, Stephan Beal  wrote: 

> On Fri, Apr 27, 2012 at 10:00 PM, Tim Streater  wrote:
>
>>  delete from addressbook where absid=(select personnick from grouplinks
>> where groupnick='27')
>
> i think what you want is IN instead of =.

Stephan,

Yes indeed - thanks!

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

Re: [sqlite] Using a select with 'where'

[Stephan Beal]

On Fri, Apr 27, 2012 at 10:00 PM, Tim Streater  wrote:

>  delete from addressbook where absid=(select personnick from grouplinks
> where groupnick='27')
>

i think what you want is IN instead of =.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users