Simon, I liked your method of attack with the 2 tables. :)
On Nov 22, 2012 6:59 AM, "Simon Slavin" wrote:
>
> On 22 Nov 2012, at 10:47am, Bart Smissaert
> wrote:
>
> > Ignore this e-mail, it was a simple mistake from my side and nil to do
> > with SQLite.
>
> It takes a big man to admit a mistak
Did a bit of further testing and when the age field is an integer field
and the band field is another field with text datatype then the method
with a lookup table is slightly faster. Either way there isn't much in it.
RBS
On 11/22/12, Simon Slavin wrote:
>
> On 22 Nov 2012, at 5:53pm, Bart Smiss
On 22 Nov 2012, at 5:53pm, Bart Smissaert wrote:
> Had a look at this, but it looks the method with select case etc. is
> faster, maybe some 20%. This is even without setting up the lookup
> table.
Fair enough.
> Also I am not what the benefit is of the order by in your sql.
> Doing this:
> up
Had a look at this, but it looks the method with select case etc. is
faster, maybe some 20%. This is even without setting up the lookup
table.
Also I am not what the benefit is of the order by in your sql.
Doing this:
update xxx set band =
(select band from convert_age ca
where xxx.band = ca.age)
> far more quickly using SQL and a second table
Not sure this is so in my particular case as the age to ageband
conversion is variable,
so the lookup table will need to be created and populated every time.
I suppose common
ones such as the 0 to 10, 11 to 20 etc. could be kept for future use,
but t
On 22 Nov 2012, at 10:47am, Bart Smissaert wrote:
> Ignore this e-mail, it was a simple mistake from my side and nil to do
> with SQLite.
It takes a big man to admit a mistake. Thanks for saving us the time of
figuring out your problem.
By the way ... your original question talks about a pro
:) Thanks.
On Thu, Nov 22, 2012 at 12:28 PM, Simon Davies wrote:
> On 22 November 2012 11:20, Staffan Tylen wrote:
> > I run this (sql.txt):
> >
> > create table table1 (age_band);
> > insert into table1 values(31);
> > UPDATE table1 SET age_band = (case when age_band <= 10 then ' 0 - 10'
> >
On 22 November 2012 11:20, Staffan Tylen wrote:
> I run this (sql.txt):
>
> create table table1 (age_band);
> insert into table1 values(31);
> UPDATE table1 SET age_band = (case when age_band <= 10 then ' 0 - 10'
> when age_band BETWEEN 11 AND 20 then ' 11 - 20' when age_band BETWEEN
> 21 AND 30
I run this (sql.txt):
create table table1 (age_band);
insert into table1 values(31);
UPDATE table1 SET age_band = (case when age_band <= 10 then ' 0 - 10'
when age_band BETWEEN 11 AND 20 then ' 11 - 20' when age_band BETWEEN
21 AND 30 then ' 21 - 30' when age_band BETWEEN 31 AND 40 then ' 31
-
Bart Smissaert wrote:
> Have a table with one field holding numbers from 0 to about 110.
> The field has no data type, so it is not integer or text.
> [...]
> I thought that as the field has no date affinity, it should be handled
> as integer numbers
Fields without affinity are never converted wit
The simple explanation was that I had make the field text instead of no
data affinity.
RBS
On 11/22/12, Bart Smissaert wrote:
> Ignore this e-mail, it was a simple mistake from my side and nil to do
> with SQLite.
> Apologies for this.
>
> RBS
>
___
sq
Ignore this e-mail, it was a simple mistake from my side and nil to do
with SQLite.
Apologies for this.
RBS
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Interesting. I run the same SQL (except I used table t1 instead of table1
'cos I'm lazy) and got this (SQLite 3.7.15):
sqlite> delete from t1;
sqlite> select * from t1;
sqlite> insert into t1 values(1);
sqlite> insert into t1 values(10);
sqlite> insert into t1 values(11);
sqlite> insert into t1 va
Have a table with one field holding numbers from 0 to about 110.
The field has no data type, so it is not integer or text.
Now I run the following SQL:
UPDATE table1 SET age_band = (case when age_band <= 10 then ' 0 - 10'
when age_band BETWEEN 11 AND 20 then ' 11 - 20' when age_band BETWEEN
21 AN
14 matches
Mail list logo
