On Mon, 18 Nov 2013 14:08:28 +0200, RSmith <rsm...@rsweb.co.za> wrote:
>Well this is the reason for my initial misunderstanding - which I then thought >I had wrong, but either you have it wrong too... or I >had it right in the first place. Ok, less cryptically now: > >It all depends on whether he has a Column called "name" that might be Null, or >whether he has a parameter which checks column "name" >and which might be null... in one case (your's) we check for null values in >the column and in another case (my later case) we check >if the parameter is null, not the column, and then from that decide whether to >use it as a check or not - not sure which but between >your and my solutions both are covered though, so I hope the OP gets sorted >out - if not, let us know... Yeah, I didn't go back far enough in the discussion, so I missed part of the spec. I'm sure the OP will sort it out after so many hints :) >On 2013/11/18 13:55, Kees Nuyt wrote: >> On Mon, 18 Nov 2013 13:04:31 +0200, RSmith <rsm...@rsweb.co.za> wrote: >> >>> Oops, misprint... >>> >>> name won't be null of course, the parameter needs to be null, kindly >>> replace the queries offered like this: >>> >>> delete from emp where ( key = ?1 ) AND (( ?2 IS NULL ) OR ( name = ?2 >>> )); >>> >>> or in the second form: >>> >>> delete from emp where ( key = ?1 ) AND (( ?2 = '' ) OR ( name = ?2 )); >>> >>> I think this is closer to the intended - thanks, >>> Ryan >> Uhm, I think you mean: >> >> delete from emp >> where ( key = ?1 ) AND (( name IS NULL ) OR ( name = ?2 )); >> >> delete from emp >> where ( key = ?1 ) AND (( name = '' ) OR ( name = ?2 )); >> >> To cover both NULL and empty: >> >> delete from emp >> where key = ?1 AND (name IS NULL OR name = '' OR name = ?2); >> >> >>> On 2013/11/18 12:56, RSmith wrote: >>>> I might be missing something extraordinarily obvious... but I cannot >>>> understand the use case for this logic you have. >>>> >>>> My first response was to just use "delete from emp where key=123" and be >>>> done with it, who cares what the name is, right? >>>> >>>> But then it dawned on me that you may for some reason have that key NOT as >>>> a unique key, which means you can have many keys that >>>> are 123 in which case delete where key = 123 will remove all of them, but >>>> adding a name as an optional second parameter/check now >>>> makes sense. >>>> >>>> Some old-school boolean logic to the rescue then: >>>> If this last case is true: >>>> >>>> delete from emp where (key = '123') AND ((name IS NULL) OR (name = >>>> 'abc')); >>>> >>>> >>>> will simply delete all keys with 123 values, but only if the name is >>>> either not specified, or the name is both specified and >>>> specific. >>>> >>>> >>>> >>>> Be careful that you might not be binding null values, but maybe empty >>>> strings in stead of values, so another solution might be: >>>> >>>> delete from emp where (key = '123') AND ((name = '') OR (name = 'abc')); >>>> >>>> >>>> You get the idea. >>>> >>>> Cheers, >>>> Ryan >>>> >>>> >>>> On 2013/11/18 09:45, d b wrote: >>>>> Hi, >>>>> >>>>> >>>>> I am trying to make single query instead of below two queries. Can >>>>> somebody help? >>>>> >>>>> 1. delete from emp where key = '123'; >>>>> 2. delete from emp where key = '123' and name = 'abc'; >>>>> >>>>> if Key available, execute 1st query. if key and name available, execute >>>>> 2nd >>>>> query. >>>>> >>>>> Is it possible to write in single query? >>>>> -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
