Re: [sqlite] What is wrong with this update SQL

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

Re: [sqlite] What is wrong with this update SQL

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

Re: [sqlite] What is wrong with this update SQL

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

Re: [sqlite] What is wrong with this update SQL

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)

Re: [sqlite] What is wrong with this update SQL

> 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

Re: [sqlite] What is wrong with this update SQL

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

Re: [sqlite] What is wrong with this update SQL

:) 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

Re: [sqlite] What is wrong with this update SQL

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

Re: [sqlite] What is wrong with this update SQL

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 -

Re: [sqlite] What is wrong with this update SQL

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

Re: [sqlite] What is wrong with this update SQL

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 >

Re: [sqlite] What is wrong with this update SQL

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

Re: [sqlite] What is wrong with this update SQL

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

[sqlite] What is wrong with this update SQL

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

Re: [sqlite] What is wrong with this UPDATE?

RB Smissaert wrote: UPDATE A3SQLADC_J SET ADDED_DATE = '' WHERE ADDED_DATE = 0 OR ADDED_DATE IS NULL, START_DATE = '' WHERE START_DATE = 0 OR START_DATE IS NULL near ",": syntax error I am sure I have run this before with no trouble. According to the web page, there should be only one WHERE

[sqlite] What is wrong with this UPDATE?

UPDATE A3SQLADC_J SET ADDED_DATE = '' WHERE ADDED_DATE = 0 OR ADDED_DATE IS NULL, START_DATE = '' WHERE START_DATE = 0 OR START_DATE IS NULL near ",": syntax error I am sure I have run this before with no trouble. RBS