Please describe in simple words what you are trying to do.
UPDATE <table> SET <field>=<value> WHERE <condition>
Will change the value of <field> in all records of the <table> that satisfy the
<condition>.
You seem to be expecting that the field is changed also in records that do not
satisfy the condition.
Or perhaps you really want something like:
BEGIN;
SELECT COUNT() FROM <table> WHERE <condition>;
if count == 0
raise exception
else
UPDATE ...
COMMIT
-----Ursprüngliche Nachricht-----
Von: techi eth [mailto:techi...@gmail.com]
Gesendet: Freitag, 02. August 2013 12:36
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Where Clause
Is their any Query format which can change the value based on conditional
expression is TRUE!!!
Thanks..
Techi -
On Fri, Aug 2, 2013 at 2:11 PM, Hick Gunter <h...@scigames.at> wrote:
> The SQL you gave translates to
>
> "Change the 'Name' field to have the value 'test' in all records where
> the 'ID' field has the value 2".
>
> If there is no such record, then SQL has nothing to do and very
> successfully does exactly nothing.
>
> SQL is "set oriented" and update operations on empty sets always succeed.
>
> I guess you are thinking along procedural/file oriented lines as in
> pseudocode
>
> record.id = 2;
> error = readwithlock( file, byID, &record );
> if (error) raise exception;
> record.name = 'test';
> error = updatewithunlock ( file, &record );
> if (error) raise exception;
>
> SQLite implements UPDATE as a SELECT followed by REPLACE statemenst as
> if written like
>
> (INSERT OR) REPLACE INTO table SELECT id,'test' FROM table WHERE
> id = 2;
>
> Which is equivalent to (naïve, not using index) pseudocode like
>
> open( file );
> while (! eof( file ) )
> {
> error = readseqwithlock( file, &record );
> if (error) raise exception;
> if (record.id == 2)
> {
> record.name = 'test';
> error = updatewithunlock( file, &record );
> if (error) raise exception;
> } else {
> error = unlock( file );
> if (error) raise exception;
> }
> }
>
> HTH
> Gunter
>
> -----Ursprüngliche Nachricht-----
> Von: techi eth [mailto:techi...@gmail.com]
> Gesendet: Freitag, 02. August 2013 10:15
> An: General Discussion of SQLite Database
> Betreff: [sqlite] Where Clause
>
> Come across one issue with conditional query execution.
>
> Query: UPDATE COMPANY SET Name= 'test' WHERE ID = 2; According to my
> understanding if no ID = 2 is present in table then error should
> return but it return with SQLITE_OK however Name value is not changed.
>
> Cheers -
> Techi
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ----------------------------------------------------------------------
> ----
> Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> Klitschgasse 2 - 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This e-mail is confidential and may well also be legally privileged.
> If you have received it in error, you are on notice as to its status
> and accordingly please notify us immediately by reply e-mail and then
> delete this message from your system. Please do not copy it or use it
> for any purposes, or disclose its contents to any person as to do so
> could be a breach of confidence. Thank you for your cooperation.
> _______________________________________________
> 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
--------------------------------------------------------------------------
Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This e-mail is confidential and may well also be legally privileged. If you
have received it in error, you are on notice as to its status and accordingly
please notify us immediately by reply e-mail and then delete this message from
your system. Please do not copy it or use it for any purposes, or disclose its
contents to any person as to do so could be a breach of confidence. Thank you
for your cooperation.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
