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? >>> >>> Regards, >>> va >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >_______________________________________________ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
