[yunqa.de] Re: DiSqlite3 - Limit not working with update on views
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Thu, 27 May 2010 21:53:18 +0200
At 13:54 27.05.2010, Clyde England wrote:
I believe you have discovered a problem in the SQLite core. I share your view
that, according to the documentation, the LIMIT clause should work on view just
as it does on plain tables.
However, since the UPDATE ... LIMIT clause is only compiled in on demand and is
not part of the default settings, I suspect that it is not as well tested as
other parts of the library.
I have forwarded the problem to the SQLite mailing list and will report back
here any progress there.
Ralf
>I have a view that uses "instead of" update triggers to update the undelying
>table.
>
>The following works fine and as expected updates all the records in the
>underlying table
>
>Update table myview set data = 'testing'
>
>However, the following does not update *any* records
>
>Update table myview set data = "something else" limit 10
>
>I would expect that this update query should update at least 10 records, but
>no records in the database are updated.
>
>It would appear that adding the limit clause causes the update statement to
>just "do nothing"
>
>I am testing this with the latest version of SQLiteSpy
>
>Reading the DiSqlite3 docs I can't see that there in such limitation when
>updating a view via triggers.
>
>Is this a bug, known limitation, or just my ignorance in doing something wrong?
>
>Thanks
>Clyde
>
>Here is some sample code to run in SQLiteSpy that will exhibit the problem
>
>create table test (data,rownum integer);
>insert into test values ('one',1);
>insert into test values ('two',2);
>create view vtest as select * from test;
>CREATE TRIGGER Trig1 instead OF update of data ON vtest
>BEGIN
>UPDATE test SET data = new.data WHERE rownum = new.rownum ;
>END;
>update vtest set data = 'yyy'; --works
>update vtest set data = 'zzz' limit 1; -- does not work
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
