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