RE: [sqlite] Limit statement size?

[Joe Wilson]

I forgot to specify the unique key for the table to be updated 
in the first attempt at INSERT OR REPLACE.

Please try this...

insert or replace into A3Test115_J(
  PATIENT_ID, ---<---
  ENTRY_ID_E2, 
  READ_CODE_E2, 
  TERM_TEXT_E2,
  ...
  NUMERIC_VALUE_E15
 )
 select
  t1.PATIENT_ID,  ---<---
  g2.ENTRY_ID,
  g2.READ_CODE,
  g2.TERM_TEXT,
  ...
  g15.NUMERIC_VALUE
 from 
  A3Test115_J t1, 
  GROUP_2 g2, 
  GROUP_3 g3,
  ... 
  GROUP_15 g15
 where
  t1.PATIENT_ID = g2.PID
  and t1.PATIENT_ID = g3.PID
  and t1.PATIENT_ID = g4.PID
  and t1.PATIENT_ID = g5.PID
  ...
  and t1.PATIENT_ID = g15.PID

--- RB Smissaert <[EMAIL PROTECTED]> wrote:

> Thanks, will have a look at that.
> It definitely looks better.
> 
> RBS


 

Never miss an email again!
Yahoo! Toolbar alerts you the instant new Mail arrives.
http://tools.search.yahoo.com/toolbar/features/mail/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Limit statement size?

[Joe Wilson]

--- Joe Wilson <[EMAIL PROTECTED]> wrote:
> I think this technique might be more compact and efficient than the update:

I think my suggestion may result in duplicate rows.

If the INSERT or REPLACE doesn't work, you might try:

 1. BEGIN
 2. dump the joined select into a temp table
 3. delete the affected rows from A3Test115_J 
 4. insert the rows from the temp table into A3Test115_J
 5. drop the temp table.
 6. COMMIT

But it might not be worth the trouble.

> insert or replace into A3Test115_J(
>   ENTRY_ID_E2, 
>   READ_CODE_E2, 
>   TERM_TEXT_E2,
>   ...
>   NUMERIC_VALUE_E15
>  )
>  select 
>   g2.ENTRY_ID,
>   g2.READ_CODE,
>   g2.TERM_TEXT,
>   ...
>   g15.NUMERIC_VALUE
>  from 
>   A3Test115_J t1, 
>   GROUP_2 g2, 
>   GROUP_3 g3,
>   ... 
>   GROUP_15 g15
>  where
>   t1.PATIENT_ID = g2.PID
>   and t1.PATIENT_ID = g3.PID
>   and t1.PATIENT_ID = g4.PID
>   and t1.PATIENT_ID = g5.PID
>   ...
>   and t1.PATIENT_ID = g15.PID
> 
> --- RB Smissaert <[EMAIL PROTECTED]> wrote:
> > UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM  GROUP_2 T WHERE
> > PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM GROUP_2 T WHERE
> > PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM GROUP_2 T WHERE
> > PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM GROUP_2 T WHERE
> > PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM GROUP_2 T WHERE
> > PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE FROM GROUP_2 T
> > WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID FROM GROUP_3 T
> > WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE FROM GROUP_3 T
> > WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT FROM GROUP_3 T
> > WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE FROM GROUP_3 T
> > WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE FROM GROUP_3 T
> > WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT NUMERIC_VALUE FROM
> > GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT ENTRY_ID FROM
> > GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT READ_CODE FROM
> > GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT TERM_TEXT FROM
> > GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT START_DATE FROM
> > GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT ADDED_DATE FROM
> > GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT
> > NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 =
> > (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID), READ_CODE_E5 =
> > (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E5 =
> > (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID), START_DATE_E5 =
> > (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E5 =
> > (SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> > NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE PATIENT_ID =
> > T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE PATIENT_ID =
> > T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE PATIENT_ID =
> > T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE PATIENT_ID =
> > T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T WHERE PATIENT_ID =
> > T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T WHERE PATIENT_ID =
> > T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE
> > PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM GROUP_7 T WHERE
> > PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM GROUP_7 T WHERE
> > PATIENT_ID = T.PID), TERM_TEXT_E7 = (SELECT TERM_TEXT FROM GROUP_7 T WHERE
> > PATIENT_ID = T.PID), START_DATE_E7 = (SELECT START_DATE FROM GROUP_7 T WHERE
> > PATIENT_ID = T.PID), ADDED_DATE_E7 = (SELECT ADDED_DATE FROM GROUP_7 T WHERE
> > PATIENT_ID = T.PID), NUMERIC_VALUE_E7 = (SELECT NUMERIC_VALUE FROM GROUP_7 T
> > WHERE PATIENT_ID = T.PID), ENTRY_ID_E8 = (SELECT ENTRY_ID FROM GROUP_8 T
> > WHERE PATIENT_ID = T.PID), READ_CODE_E8 = (SELECT READ_CODE FROM GROUP_8 T
> > WHERE PATIENT_ID = T.PID), TERM_TEXT_E8 = (SELECT TERM_TEXT FROM GROUP_8 T
> > WHERE PATIENT_ID = T.PID), START_DATE_E8 = (SELECT START_DATE FROM GROUP_8 T
> > WHERE PATIENT_ID = T.PID), ADDED_DATE_E8 = (SELECT ADDED_DATE FROM GROUP_8 T
> > WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E8 = (SELECT NUMERIC_VALUE FROM
> > GROUP_8 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E9 = (SELECT ENTRY_ID FROM
> > GROUP_9 T WHERE PATIENT_ID = T.PID), READ_CODE_E9 = (SELECT READ_CODE FROM
> > GROUP_9 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E9 = (SELECT TERM_TEXT FROM
> > GROUP_9 T WHERE PATIENT_ID = T.PID), START_DATE_E9 = (SELECT START_DATE FROM
> > GROUP_9 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E9 = (SELECT ADDED_DATE FROM
> > GROUP_9 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E9 = (SELECT
> > NUMERIC_VALUE FROM GROUP_9 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E10 =
> > (SELECT ENTRY_ID FROM GROUP_10 T WHERE PATIENT_ID = T.PID), READ_CODE_E10 =
> > 

RE: [sqlite] Limit statement size?

[RB Smissaert]

Thanks, will have a look at that.
It definitely looks better.

RBS


-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: 28 January 2007 20:10
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Limit statement size?

I think this technique might be more compact and efficient than the update:

insert or replace into A3Test115_J(
  ENTRY_ID_E2, 
  READ_CODE_E2, 
  TERM_TEXT_E2,
  ...
  NUMERIC_VALUE_E15
 )
 select 
  g2.ENTRY_ID,
  g2.READ_CODE,
  g2.TERM_TEXT,
  ...
  g15.NUMERIC_VALUE
 from 
  A3Test115_J t1, 
  GROUP_2 g2, 
  GROUP_3 g3,
  ... 
  GROUP_15 g15
 where
  t1.PATIENT_ID = g2.PID
  and t1.PATIENT_ID = g3.PID
  and t1.PATIENT_ID = g4.PID
  and t1.PATIENT_ID = g5.PID
  ...
  and t1.PATIENT_ID = g15.PID

--- RB Smissaert <[EMAIL PROTECTED]> wrote:
> UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM  GROUP_2 T
WHERE
> PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM GROUP_2 T WHERE
> PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM GROUP_2 T WHERE
> PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM GROUP_2 T
WHERE
> PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM GROUP_2 T
WHERE
> PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE FROM GROUP_2
T
> WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE FROM GROUP_3
T
> WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE FROM GROUP_3
T
> WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT NUMERIC_VALUE FROM
> GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT ENTRY_ID FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT READ_CODE FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT TERM_TEXT FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT START_DATE
FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT ADDED_DATE
FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT
> NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 =
> (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID), READ_CODE_E5 =
> (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E5 =
> (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID), START_DATE_E5
=
> (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E5
=
> (SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE PATIENT_ID =
> T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE PATIENT_ID =
> T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE PATIENT_ID =
> T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE PATIENT_ID =
> T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T WHERE PATIENT_ID
=
> T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T WHERE PATIENT_ID
=
> T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE
> PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM GROUP_7 T WHERE
> PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM GROUP_7 T WHERE
> PATIENT_ID = T.PID), TERM_TEXT_E7 = (SELECT TERM_TEXT FROM GROUP_7 T WHERE
> PATIENT_ID = T.PID), START_DATE_E7 = (SELECT START_DATE FROM GROUP_7 T
WHERE
> PATIENT_ID = T.PID), ADDED_DATE_E7 = (SELECT ADDED_DATE FROM GROUP_7 T
WHERE
> PATIENT_ID = T.PID), NUMERIC_VALUE_E7 = (SELECT NUMERIC_VALUE FROM GROUP_7
T
> WHERE PATIENT_ID = T.PID), ENTRY_ID_E8 = (SELECT ENTRY_ID FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), READ_CODE_E8 = (SELECT READ_CODE FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), TERM_TEXT_E8 = (SELECT TERM_TEXT FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), START_DATE_E8 = (SELECT START_DATE FROM GROUP_8
T
> WHERE PATIENT_ID = T.PID), ADDED_DATE_E8 = (SELECT ADDED_DATE FROM GROUP_8
T
> WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E8 = (SELECT NUMERIC_VALUE FROM
> GROUP_8 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E9 = (SELECT ENTRY_ID FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), READ_CODE_E9 = (SELECT READ_CODE FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E9 = (SELECT TERM_TEXT FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), START_DATE_E9 = (SELECT START_DATE
FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E9 = (SELECT ADDED_DATE
FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E9 = (SELECT
> NUMERIC_VALUE FROM GROUP_9 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E10 =
> (SELECT ENTRY_ID FROM GROUP_10 T WHERE PATIENT_ID = T.PID), READ_CODE_E10
=
> (SELECT READ_CODE FROM GROUP_10 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E10
=
> (SELECT TERM_TEXT FROM GROUP_10 T WHERE PATIENT_ID = T.PID),
START_DATE_E10
> = (SELECT START_DATE FROM GROUP_10 T WHERE PATIENT_ID = T.PID),
> ADDED_DATE_E10 = (SELECT ADDED_DATE FROM GROUP_10 T WHERE PATIENT_ID =
> T.PID), 

Re: [sqlite] Limit statement size?

[Joe Wilson]

I think this technique might be more compact and efficient than the update:

insert or replace into A3Test115_J(
  ENTRY_ID_E2, 
  READ_CODE_E2, 
  TERM_TEXT_E2,
  ...
  NUMERIC_VALUE_E15
 )
 select 
  g2.ENTRY_ID,
  g2.READ_CODE,
  g2.TERM_TEXT,
  ...
  g15.NUMERIC_VALUE
 from 
  A3Test115_J t1, 
  GROUP_2 g2, 
  GROUP_3 g3,
  ... 
  GROUP_15 g15
 where
  t1.PATIENT_ID = g2.PID
  and t1.PATIENT_ID = g3.PID
  and t1.PATIENT_ID = g4.PID
  and t1.PATIENT_ID = g5.PID
  ...
  and t1.PATIENT_ID = g15.PID

--- RB Smissaert <[EMAIL PROTECTED]> wrote:
> UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM  GROUP_2 T WHERE
> PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM GROUP_2 T WHERE
> PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM GROUP_2 T WHERE
> PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM GROUP_2 T WHERE
> PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM GROUP_2 T WHERE
> PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE FROM GROUP_2 T
> WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT NUMERIC_VALUE FROM
> GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT ENTRY_ID FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT READ_CODE FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT TERM_TEXT FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT START_DATE FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT ADDED_DATE FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT
> NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 =
> (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID), READ_CODE_E5 =
> (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E5 =
> (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID), START_DATE_E5 =
> (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E5 =
> (SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE PATIENT_ID =
> T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE PATIENT_ID =
> T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE PATIENT_ID =
> T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE PATIENT_ID =
> T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T WHERE PATIENT_ID =
> T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T WHERE PATIENT_ID =
> T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE
> PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM GROUP_7 T WHERE
> PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM GROUP_7 T WHERE
> PATIENT_ID = T.PID), TERM_TEXT_E7 = (SELECT TERM_TEXT FROM GROUP_7 T WHERE
> PATIENT_ID = T.PID), START_DATE_E7 = (SELECT START_DATE FROM GROUP_7 T WHERE
> PATIENT_ID = T.PID), ADDED_DATE_E7 = (SELECT ADDED_DATE FROM GROUP_7 T WHERE
> PATIENT_ID = T.PID), NUMERIC_VALUE_E7 = (SELECT NUMERIC_VALUE FROM GROUP_7 T
> WHERE PATIENT_ID = T.PID), ENTRY_ID_E8 = (SELECT ENTRY_ID FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), READ_CODE_E8 = (SELECT READ_CODE FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), TERM_TEXT_E8 = (SELECT TERM_TEXT FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), START_DATE_E8 = (SELECT START_DATE FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), ADDED_DATE_E8 = (SELECT ADDED_DATE FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E8 = (SELECT NUMERIC_VALUE FROM
> GROUP_8 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E9 = (SELECT ENTRY_ID FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), READ_CODE_E9 = (SELECT READ_CODE FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E9 = (SELECT TERM_TEXT FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), START_DATE_E9 = (SELECT START_DATE FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E9 = (SELECT ADDED_DATE FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E9 = (SELECT
> NUMERIC_VALUE FROM GROUP_9 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E10 =
> (SELECT ENTRY_ID FROM GROUP_10 T WHERE PATIENT_ID = T.PID), READ_CODE_E10 =
> (SELECT READ_CODE FROM GROUP_10 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E10 =
> (SELECT TERM_TEXT FROM GROUP_10 T WHERE PATIENT_ID = T.PID), START_DATE_E10
> = (SELECT START_DATE FROM GROUP_10 T WHERE PATIENT_ID = T.PID),
> ADDED_DATE_E10 = (SELECT ADDED_DATE FROM GROUP_10 T WHERE PATIENT_ID =
> T.PID), NUMERIC_VALUE_E10 = (SELECT NUMERIC_VALUE FROM GROUP_10 T WHERE
> PATIENT_ID = T.PID), ENTRY_ID_E11 = (SELECT ENTRY_ID FROM GROUP_11 T WHERE
> PATIENT_ID = T.PID), READ_CODE_E11 = (SELECT READ_CODE FROM GROUP_11 T WHERE
> PATIENT_ID = T.PID), 

RE: [sqlite] Limit statement size?

[RB Smissaert]

> The UPDATE statement in the original example is a way to
> compensate for SQLite's inability to perform a join on
> another table before doing an UPDATE.

Yes, I remember trying to do this at first via joins, but I couldn't
get that to work and ended up with the current solution.
Still, it wouldn't surprise me if there was a better way to do this.

Simplified, this is what I am trying to do:

Given a table like this:

PID DATECODE
--
1   20030101aa
1   20051112dge
2   19980505dd
3   20010808ee
3   20031212mm

I need to transpose this to get only unique PID's in column 1 and the other
data grouped in the same row, like this:

PID DATE_1  CODE_1  DATE_2  CODE_2

1   20030101aa  20051112dge
2   19980505dd
3   20010808ee  20031212mm

Etc.

The number of rows per PID is variable and that can be up to maybe 100.
Also the number of different items per row as in the first table is variable
and that could be up to maybe 10.

RBS


-Original Message-
From: Joe Wilson [mailto:[EMAIL PROTECTED] 
Sent: 28 January 2007 19:14
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Limit statement size?

--- Fred Williams <[EMAIL PROTECTED]> wrote:
> Wow!  Talk about obfuscated code!  I didn't even try to dig deeper than
> a quick scan, but could this abomination be broken into multiple update
> queries?  On the surface it looks like each "group" is unique.  If so,
> wouldn't a transaction with multiple update statements be much more
> efficient and a much lighter load on resources on a step by step basis?
> I damn well know it would be much more pleasing to the eye! :-)

It's not complicated at all.

The UPDATE statement in the original example is a way to compensate
for SQLite's inability to perform a join on another table before doing 
an UPDATE. That's why the author needed all those inner selects each 
returning a single value.

 http://www.sqlite.org/lang_update.html

Had the MySQL UPDATE syntax been available, the same UPDATE statement
could be expressed in less than 10% of the SQL in the original 
SQL statement, with far fewer database accesses due to the reduced 
number of SQL subqueries.

 http://dev.mysql.com/doc/refman/5.0/en/update.html

Another alternative is for the SQLite queryer to first query all those
other tables first and then programmatically (via C or whatever)
construct and execute the UPDATE statement. But I can understand why 
anyone would want to avoid this extra work just to perform a 
straight-forward UPDATE.

> 
> Fred
> 
> > -Original Message-
> > From: RB Smissaert [mailto:[EMAIL PROTECTED]
> > Sent: Sunday, January 28, 2007 12:07 PM
> > To: sqlite-users@sqlite.org
> > Subject: [sqlite] Limit statement size?
> >
> >
> > Is there any limit on the size of the SQL statements in SQLite?
> > Didn't think this would come into play, but have now come across this
> > query and wonder if this needs considering:
> >
> > UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM
> > GROUP_2 T WHERE
> > PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM
> > GROUP_2 T WHERE
> > PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM
> > GROUP_2 T WHERE
> > PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM
> > GROUP_2 T WHERE
> > PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM
> > GROUP_2 T WHERE
> > PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE
> > FROM GROUP_2 T
> > WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID
> > FROM GROUP_3 T
> > WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE
> > FROM GROUP_3 T
> > WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT
> > FROM GROUP_3 T
> > WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE
> > FROM GROUP_3 T
> > WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE
> > FROM GROUP_3 T
> > WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT
> > NUMERIC_VALUE FROM
> > GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT
> > ENTRY_ID FROM
> > GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT
> > READ_CODE FROM
> > GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT
> > TERM_TEXT FROM
> > GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT
> > START_DATE FROM
> > GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT
> > ADDED_DATE FROM
> > GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT
> > NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 =
> > (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> > READ_CODE_E5 =
> > (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> > TERM_TEXT_E5 =
> > (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> > START_DATE_E5 =
> > (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> > 

RE: [sqlite] Limit statement size?

[Joe Wilson]

--- Fred Williams <[EMAIL PROTECTED]> wrote:
> Wow!  Talk about obfuscated code!  I didn't even try to dig deeper than
> a quick scan, but could this abomination be broken into multiple update
> queries?  On the surface it looks like each "group" is unique.  If so,
> wouldn't a transaction with multiple update statements be much more
> efficient and a much lighter load on resources on a step by step basis?
> I damn well know it would be much more pleasing to the eye! :-)

It's not complicated at all.

The UPDATE statement in the original example is a way to compensate
for SQLite's inability to perform a join on another table before doing 
an UPDATE. That's why the author needed all those inner selects each 
returning a single value.

 http://www.sqlite.org/lang_update.html

Had the MySQL UPDATE syntax been available, the same UPDATE statement
could be expressed in less than 10% of the SQL in the original 
SQL statement, with far fewer database accesses due to the reduced 
number of SQL subqueries.

 http://dev.mysql.com/doc/refman/5.0/en/update.html

Another alternative is for the SQLite queryer to first query all those
other tables first and then programmatically (via C or whatever)
construct and execute the UPDATE statement. But I can understand why 
anyone would want to avoid this extra work just to perform a 
straight-forward UPDATE.

> 
> Fred
> 
> > -Original Message-
> > From: RB Smissaert [mailto:[EMAIL PROTECTED]
> > Sent: Sunday, January 28, 2007 12:07 PM
> > To: sqlite-users@sqlite.org
> > Subject: [sqlite] Limit statement size?
> >
> >
> > Is there any limit on the size of the SQL statements in SQLite?
> > Didn't think this would come into play, but have now come across this
> > query and wonder if this needs considering:
> >
> > UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM
> > GROUP_2 T WHERE
> > PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM
> > GROUP_2 T WHERE
> > PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM
> > GROUP_2 T WHERE
> > PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM
> > GROUP_2 T WHERE
> > PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM
> > GROUP_2 T WHERE
> > PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE
> > FROM GROUP_2 T
> > WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID
> > FROM GROUP_3 T
> > WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE
> > FROM GROUP_3 T
> > WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT
> > FROM GROUP_3 T
> > WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE
> > FROM GROUP_3 T
> > WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE
> > FROM GROUP_3 T
> > WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT
> > NUMERIC_VALUE FROM
> > GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT
> > ENTRY_ID FROM
> > GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT
> > READ_CODE FROM
> > GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT
> > TERM_TEXT FROM
> > GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT
> > START_DATE FROM
> > GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT
> > ADDED_DATE FROM
> > GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT
> > NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 =
> > (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> > READ_CODE_E5 =
> > (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> > TERM_TEXT_E5 =
> > (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> > START_DATE_E5 =
> > (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> > ADDED_DATE_E5 =
> > (SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> > NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE
> > PATIENT_ID =
> > T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE
> > PATIENT_ID =
> > T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE
> > PATIENT_ID =
> > T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE
> > PATIENT_ID =
> > T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T
> > WHERE PATIENT_ID =
> > T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T
> > WHERE PATIENT_ID =
> > T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE
> > PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM
> > GROUP_7 T WHERE
> > PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM
> > GROUP_7 T WHERE
> > PATIENT_ID = T.PID), TERM_TEXT_E7 = (SELECT TERM_TEXT FROM
> > GROUP_7 T WHERE
> > PATIENT_ID = T.PID), START_DATE_E7 = (SELECT START_DATE FROM
> > GROUP_7 T WHERE
> > PATIENT_ID = T.PID), ADDED_DATE_E7 = (SELECT ADDED_DATE FROM
> > GROUP_7 T WHERE
> > PATIENT_ID = T.PID), NUMERIC_VALUE_E7 = (SELECT NUMERIC_VALUE
> > FROM GROUP_7 T
> > WHERE PATIENT_ID = T.PID), ENTRY_ID_E8 = (SELECT ENTRY_ID
> > FROM GROUP_8 T
> > WHERE PATIENT_ID = T.PID), READ_CODE_E8 = (SELECT READ_CODE
> > FROM GROUP_8 T
> > WHERE PATIENT_ID = 

RE: [sqlite] Limit statement size?

[RB Smissaert]

I don't think normalization comes into play here.
These are a whole of manipulations to transpose a table.
My normal base tables are all normalized fine.

RBS


-Original Message-
From: Clay Dowling [mailto:[EMAIL PROTECTED] 
Sent: 28 January 2007 19:01
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Limit statement size?

You have any way to normalize that original source table?  It's full of
extents, which is the first sign of badness in a table design and
assured of complicating your life.

Clay

RB Smissaert wrote:
> Yes, I agree it looks messy, but I used to do this in steps and after
advice
> I think from Igor Tandenik I lumped it all together and run it in one go,
> which is a lot faster.
> 
> RBS
> 
> -Original Message-
> From: Fred Williams [mailto:[EMAIL PROTECTED] 
> Sent: 28 January 2007 18:49
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Limit statement size?
> 
> Wow!  Talk about obfuscated code!  I didn't even try to dig deeper than
> a quick scan, but could this abomination be broken into multiple update
> queries?  On the surface it looks like each "group" is unique.  If so,
> wouldn't a transaction with multiple update statements be much more
> efficient and a much lighter load on resources on a step by step basis?
> I damn well know it would be much more pleasing to the eye! :-)
> 
> Fred
> 
>> -Original Message-
>> From: RB Smissaert [mailto:[EMAIL PROTECTED]
>> Sent: Sunday, January 28, 2007 12:07 PM
>> To: sqlite-users@sqlite.org
>> Subject: [sqlite] Limit statement size?
>>
>>
>> Is there any limit on the size of the SQL statements in SQLite?
>> Didn't think this would come into play, but have now come across this
>> query and wonder if this needs considering:
>>
>> UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM
>> GROUP_2 T WHERE
>> PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM
>> GROUP_2 T WHERE
>> PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM
>> GROUP_2 T WHERE
>> PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM
>> GROUP_2 T WHERE
>> PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM
>> GROUP_2 T WHERE
>> PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE
>> FROM GROUP_2 T
>> WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID
>> FROM GROUP_3 T
>> WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE
>> FROM GROUP_3 T
>> WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT
>> FROM GROUP_3 T
>> WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE
>> FROM GROUP_3 T
>> WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE
>> FROM GROUP_3 T
>> WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT
>> NUMERIC_VALUE FROM
>> GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT
>> ENTRY_ID FROM
>> GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT
>> READ_CODE FROM
>> GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT
>> TERM_TEXT FROM
>> GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT
>> START_DATE FROM
>> GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT
>> ADDED_DATE FROM
>> GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT
>> NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 =
>> (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
>> READ_CODE_E5 =
>> (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
>> TERM_TEXT_E5 =
>> (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
>> START_DATE_E5 =
>> (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
>> ADDED_DATE_E5 =
>> (SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
>> NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE
>> PATIENT_ID =
>> T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE
>> PATIENT_ID =
>> T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE
>> PATIENT_ID =
>> T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE
>> PATIENT_ID =
>> T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T
>> WHERE PATIENT_ID =
>> T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T
>> WHERE PATIENT_ID =
>> T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE
>> PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM
>> GROUP_7 T WHERE
>> PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM
>> GROUP_7 T WHERE
>> PATIENT_ID = T.PID), TERM_TEXT_E7 = (SELECT TERM_TEXT FROM
>> GROUP_7 T WHERE
>> PATIENT_ID = T.PID), START_DATE_E7 = (SELECT START_DATE FROM
>> GROUP_7 T WHERE
>> PATIENT_ID = T.PID), ADDED_DATE_E7 = (SELECT ADDED_DATE FROM
>> GROUP_7 T WHERE
>> PATIENT_ID = T.PID), NUMERIC_VALUE_E7 = (SELECT NUMERIC_VALUE
>> FROM GROUP_7 T
>> WHERE PATIENT_ID = T.PID), ENTRY_ID_E8 = (SELECT ENTRY_ID
>> FROM GROUP_8 T
>> WHERE PATIENT_ID = T.PID), READ_CODE_E8 = (SELECT READ_CODE
>> FROM GROUP_8 T
>> WHERE PATIENT_ID = T.PID), TERM_TEXT_E8 = (SELECT TERM_TEXT
>> FROM GROUP_8 T
>> WHERE PATIENT_ID = T.PID), 

Re: [sqlite] Limit statement size?

[Clay Dowling]

You have any way to normalize that original source table?  It's full of
extents, which is the first sign of badness in a table design and
assured of complicating your life.

Clay

RB Smissaert wrote:
> Yes, I agree it looks messy, but I used to do this in steps and after advice
> I think from Igor Tandenik I lumped it all together and run it in one go,
> which is a lot faster.
> 
> RBS
> 
> -Original Message-
> From: Fred Williams [mailto:[EMAIL PROTECTED] 
> Sent: 28 January 2007 18:49
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Limit statement size?
> 
> Wow!  Talk about obfuscated code!  I didn't even try to dig deeper than
> a quick scan, but could this abomination be broken into multiple update
> queries?  On the surface it looks like each "group" is unique.  If so,
> wouldn't a transaction with multiple update statements be much more
> efficient and a much lighter load on resources on a step by step basis?
> I damn well know it would be much more pleasing to the eye! :-)
> 
> Fred
> 
>> -Original Message-
>> From: RB Smissaert [mailto:[EMAIL PROTECTED]
>> Sent: Sunday, January 28, 2007 12:07 PM
>> To: sqlite-users@sqlite.org
>> Subject: [sqlite] Limit statement size?
>>
>>
>> Is there any limit on the size of the SQL statements in SQLite?
>> Didn't think this would come into play, but have now come across this
>> query and wonder if this needs considering:
>>
>> UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM
>> GROUP_2 T WHERE
>> PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM
>> GROUP_2 T WHERE
>> PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM
>> GROUP_2 T WHERE
>> PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM
>> GROUP_2 T WHERE
>> PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM
>> GROUP_2 T WHERE
>> PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE
>> FROM GROUP_2 T
>> WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID
>> FROM GROUP_3 T
>> WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE
>> FROM GROUP_3 T
>> WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT
>> FROM GROUP_3 T
>> WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE
>> FROM GROUP_3 T
>> WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE
>> FROM GROUP_3 T
>> WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT
>> NUMERIC_VALUE FROM
>> GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT
>> ENTRY_ID FROM
>> GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT
>> READ_CODE FROM
>> GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT
>> TERM_TEXT FROM
>> GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT
>> START_DATE FROM
>> GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT
>> ADDED_DATE FROM
>> GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT
>> NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 =
>> (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
>> READ_CODE_E5 =
>> (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
>> TERM_TEXT_E5 =
>> (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
>> START_DATE_E5 =
>> (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
>> ADDED_DATE_E5 =
>> (SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
>> NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE
>> PATIENT_ID =
>> T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE
>> PATIENT_ID =
>> T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE
>> PATIENT_ID =
>> T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE
>> PATIENT_ID =
>> T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T
>> WHERE PATIENT_ID =
>> T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T
>> WHERE PATIENT_ID =
>> T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE
>> PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM
>> GROUP_7 T WHERE
>> PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM
>> GROUP_7 T WHERE
>> PATIENT_ID = T.PID), TERM_TEXT_E7 = (SELECT TERM_TEXT FROM
>> GROUP_7 T WHERE
>> PATIENT_ID = T.PID), START_DATE_E7 = (SELECT START_DATE FROM
>> GROUP_7 T WHERE
>> PATIENT_ID = T.PID), ADDED_DATE_E7 = (SELECT ADDED_DATE FROM
>> GROUP_7 T WHERE
>> PATIENT_ID = T.PID), NUMERIC_VALUE_E7 = (SELECT NUMERIC_VALUE
>> FROM GROUP_7 T
>> WHERE PATIENT_ID = T.PID), ENTRY_ID_E8 = (SELECT ENTRY_ID
>> FROM GROUP_8 T
>> WHERE PATIENT_ID = T.PID), READ_CODE_E8 = (SELECT READ_CODE
>> FROM GROUP_8 T
>> WHERE PATIENT_ID = T.PID), TERM_TEXT_E8 = (SELECT TERM_TEXT
>> FROM GROUP_8 T
>> WHERE PATIENT_ID = T.PID), START_DATE_E8 = (SELECT START_DATE
>> FROM GROUP_8 T
>> WHERE PATIENT_ID = T.PID), ADDED_DATE_E8 = (SELECT ADDED_DATE
>> FROM GROUP_8 T
>> WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E8 = (SELECT
>> NUMERIC_VALUE FROM
>> GROUP_8 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E9 = (SELECT
>> ENTRY_ID FROM
>> GROUP_9 T WHERE PATIENT_ID = T.PID), READ_CODE_E9 

RE: [sqlite] Limit statement size?

[RB Smissaert]

Yes, I agree it looks messy, but I used to do this in steps and after advice
I think from Igor Tandenik I lumped it all together and run it in one go,
which is a lot faster.

RBS

-Original Message-
From: Fred Williams [mailto:[EMAIL PROTECTED] 
Sent: 28 January 2007 18:49
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Limit statement size?

Wow!  Talk about obfuscated code!  I didn't even try to dig deeper than
a quick scan, but could this abomination be broken into multiple update
queries?  On the surface it looks like each "group" is unique.  If so,
wouldn't a transaction with multiple update statements be much more
efficient and a much lighter load on resources on a step by step basis?
I damn well know it would be much more pleasing to the eye! :-)

Fred

> -Original Message-
> From: RB Smissaert [mailto:[EMAIL PROTECTED]
> Sent: Sunday, January 28, 2007 12:07 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Limit statement size?
>
>
> Is there any limit on the size of the SQL statements in SQLite?
> Didn't think this would come into play, but have now come across this
> query and wonder if this needs considering:
>
> UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM
> GROUP_2 T WHERE
> PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM
> GROUP_2 T WHERE
> PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM
> GROUP_2 T WHERE
> PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM
> GROUP_2 T WHERE
> PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM
> GROUP_2 T WHERE
> PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE
> FROM GROUP_2 T
> WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID
> FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE
> FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT
> FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE
> FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE
> FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT
> NUMERIC_VALUE FROM
> GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT
> ENTRY_ID FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT
> READ_CODE FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT
> TERM_TEXT FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT
> START_DATE FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT
> ADDED_DATE FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT
> NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 =
> (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> READ_CODE_E5 =
> (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> TERM_TEXT_E5 =
> (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> START_DATE_E5 =
> (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> ADDED_DATE_E5 =
> (SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE
> PATIENT_ID =
> T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE
> PATIENT_ID =
> T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE
> PATIENT_ID =
> T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE
> PATIENT_ID =
> T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T
> WHERE PATIENT_ID =
> T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T
> WHERE PATIENT_ID =
> T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE
> PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM
> GROUP_7 T WHERE
> PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM
> GROUP_7 T WHERE
> PATIENT_ID = T.PID), TERM_TEXT_E7 = (SELECT TERM_TEXT FROM
> GROUP_7 T WHERE
> PATIENT_ID = T.PID), START_DATE_E7 = (SELECT START_DATE FROM
> GROUP_7 T WHERE
> PATIENT_ID = T.PID), ADDED_DATE_E7 = (SELECT ADDED_DATE FROM
> GROUP_7 T WHERE
> PATIENT_ID = T.PID), NUMERIC_VALUE_E7 = (SELECT NUMERIC_VALUE
> FROM GROUP_7 T
> WHERE PATIENT_ID = T.PID), ENTRY_ID_E8 = (SELECT ENTRY_ID
> FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), READ_CODE_E8 = (SELECT READ_CODE
> FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), TERM_TEXT_E8 = (SELECT TERM_TEXT
> FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), START_DATE_E8 = (SELECT START_DATE
> FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), ADDED_DATE_E8 = (SELECT ADDED_DATE
> FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E8 = (SELECT
> NUMERIC_VALUE FROM
> GROUP_8 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E9 = (SELECT
> ENTRY_ID FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), READ_CODE_E9 = (SELECT
> READ_CODE FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E9 = (SELECT
> TERM_TEXT FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), START_DATE_E9 = (SELECT
> START_DATE FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E9 = (SELECT
> ADDED_DATE FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E9 = (SELECT
> 

RE: [sqlite] Limit statement size?

[RB Smissaert]

OK, thanks, size of the query shouldn't be a problem then.

Performance is fine as well. This particular query ran in 1.4 secs, which
in my app is fine as it is reporting software and results are expected to
take a bit of time.
This query is the final query of a number of queries that transpose a table
from a vertical layout to a horizontal layout and this is the best I can
come up with sofar.

Maintenance is not a problem either as this runs all automatic without any
user input.

This is what is going on to do the transpose:

CREATE TABLE 'MAX_ENTRY_COUNT' ([MEC_NODE_KEY] TEXT UNIQUE,
[MEC_MAX_ENTRY_COUNT] INTEGER)
CREATE TABLE 'A3Test115_F_COUNT' ([PATIENT_IDC] INTEGER PRIMARY KEY,
[PATIENT_COUNT] INTEGER)
INSERT INTO A3Test115_F_COUNT(PATIENT_IDC, PATIENT_COUNT) SELECT PATIENT_ID,
COUNT(PATIENT_ID) FROM A3Test115_F GROUP BY PATIENT_ID
analyze A3Test115_F_COUNT
SELECT MAX(PATIENT_COUNT) FROM A3Test115_F_COUNT
INSERT INTO MAX_ENTRY_COUNT(MEC_NODE_KEY, MEC_MAX_ENTRY_COUNT)
VALUES('3Test-1157F99B-DD0D-E64D-A154-0569C636C494', 15)
BEGIN TRANSACTION
DROP TABLE IF EXISTS GROUP_1
CREATE TABLE 'GROUP_1' ([PID] INTEGER PRIMARY KEY, [ENTRY_ID] INTEGER,
[READ_CODE] TEXT, [TERM_TEXT] TEXT, [START_DATE] INTEGER, [ADDED_DATE]
INTEGER, [NUMERIC_VALUE] REAL)
INSERT INTO GROUP_1 (PID, ENTRY_ID, READ_CODE, TERM_TEXT, START_DATE,
ADDED_DATE, NUMERIC_VALUE) SELECT T.PATIENT_ID, T.ENTRY_ID, T.READ_CODE,
T.TERM_TEXT, T.START_DATE, T.ADDED_DATE, T.NUMERIC_VALUE FROM A3Test115_F T
GROUP BY T.PATIENT_ID
CREATE INDEX IDX1_GROUP_1_ENTRY_ID ON GROUP_1(ENTRY_ID)
analyze GROUP_1
DROP TABLE IF EXISTS GROUP_2
CREATE TABLE 'GROUP_2' ([PID] INTEGER PRIMARY KEY, [ENTRY_ID] INTEGER,
[READ_CODE] TEXT, [TERM_TEXT] TEXT, [START_DATE] INTEGER, [ADDED_DATE]
INTEGER, [NUMERIC_VALUE] REAL)
INSERT INTO GROUP_2 (PID, ENTRY_ID, READ_CODE, TERM_TEXT, START_DATE,
ADDED_DATE, NUMERIC_VALUE) SELECT T.PATIENT_ID, T.ENTRY_ID, T.READ_CODE,
T.TERM_TEXT, T.START_DATE, T.ADDED_DATE, T.NUMERIC_VALUE FROM A3Test115_F T
INNER JOIN GROUP_1 G ON (T.PATIENT_ID = G.PID) WHERE T.ENTRY_ID < G.ENTRY_ID
GROUP BY T.PATIENT_ID
CREATE INDEX IDX1_GROUP_2_ENTRY_ID ON GROUP_2(ENTRY_ID)
analyze GROUP_2
SELECT COUNT(ROWID) FROM GROUP_2

Etc, etc.

Not sure if it makes it clear what is going on, but if anybody has a better
idea how to transpose a table I would be interested.


RBS


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: 28 January 2007 18:20
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Limit statement size?

"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> Is there any limit on the size of the SQL statements in SQLite?

32-bit integers are used to count things in various places.  I
don't really consider that a limit, but some people do.
See http://www.sqlite.org/cvstrac/tktview?tn=2125
There may be other limits that I do not yet know about because
nobody has yet reached them. 

> Didn't think this would come into play, but have now come across this
> query and wonder if this needs considering:

That query will likely work, though efficiency might become an issue.
More importantly, it seems like it might be hard to maintain.
I would refer the author to
http://www.en.wikipedia.org/wiki/Database_normalization as a
starting point.

> 
> UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM  GROUP_2 T
WHERE
> PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM GROUP_2 T WHERE
> PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM GROUP_2 T WHERE
> PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM GROUP_2 T
WHERE
> PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM GROUP_2 T
WHERE
> PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE FROM GROUP_2
T
> WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE FROM GROUP_3
T
> WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE FROM GROUP_3
T
> WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT NUMERIC_VALUE FROM
> GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT ENTRY_ID FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT READ_CODE FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT TERM_TEXT FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT START_DATE
FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT ADDED_DATE
FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT
> NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 =
> (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID), READ_CODE_E5 =
> (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E5 =
> (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID), START_DATE_E5
=
> (SELECT START_DATE FROM GROUP_5 T WHERE 

RE: [sqlite] Limit statement size?

[Fred Williams]

Wow!  Talk about obfuscated code!  I didn't even try to dig deeper than
a quick scan, but could this abomination be broken into multiple update
queries?  On the surface it looks like each "group" is unique.  If so,
wouldn't a transaction with multiple update statements be much more
efficient and a much lighter load on resources on a step by step basis?
I damn well know it would be much more pleasing to the eye! :-)

Fred

> -Original Message-
> From: RB Smissaert [mailto:[EMAIL PROTECTED]
> Sent: Sunday, January 28, 2007 12:07 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Limit statement size?
>
>
> Is there any limit on the size of the SQL statements in SQLite?
> Didn't think this would come into play, but have now come across this
> query and wonder if this needs considering:
>
> UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM
> GROUP_2 T WHERE
> PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM
> GROUP_2 T WHERE
> PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM
> GROUP_2 T WHERE
> PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM
> GROUP_2 T WHERE
> PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM
> GROUP_2 T WHERE
> PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE
> FROM GROUP_2 T
> WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID
> FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE
> FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT
> FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE
> FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE
> FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT
> NUMERIC_VALUE FROM
> GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT
> ENTRY_ID FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT
> READ_CODE FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT
> TERM_TEXT FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT
> START_DATE FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT
> ADDED_DATE FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT
> NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 =
> (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> READ_CODE_E5 =
> (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> TERM_TEXT_E5 =
> (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> START_DATE_E5 =
> (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> ADDED_DATE_E5 =
> (SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE
> PATIENT_ID =
> T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE
> PATIENT_ID =
> T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE
> PATIENT_ID =
> T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE
> PATIENT_ID =
> T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T
> WHERE PATIENT_ID =
> T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T
> WHERE PATIENT_ID =
> T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE
> PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM
> GROUP_7 T WHERE
> PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM
> GROUP_7 T WHERE
> PATIENT_ID = T.PID), TERM_TEXT_E7 = (SELECT TERM_TEXT FROM
> GROUP_7 T WHERE
> PATIENT_ID = T.PID), START_DATE_E7 = (SELECT START_DATE FROM
> GROUP_7 T WHERE
> PATIENT_ID = T.PID), ADDED_DATE_E7 = (SELECT ADDED_DATE FROM
> GROUP_7 T WHERE
> PATIENT_ID = T.PID), NUMERIC_VALUE_E7 = (SELECT NUMERIC_VALUE
> FROM GROUP_7 T
> WHERE PATIENT_ID = T.PID), ENTRY_ID_E8 = (SELECT ENTRY_ID
> FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), READ_CODE_E8 = (SELECT READ_CODE
> FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), TERM_TEXT_E8 = (SELECT TERM_TEXT
> FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), START_DATE_E8 = (SELECT START_DATE
> FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), ADDED_DATE_E8 = (SELECT ADDED_DATE
> FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E8 = (SELECT
> NUMERIC_VALUE FROM
> GROUP_8 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E9 = (SELECT
> ENTRY_ID FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), READ_CODE_E9 = (SELECT
> READ_CODE FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E9 = (SELECT
> TERM_TEXT FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), START_DATE_E9 = (SELECT
> START_DATE FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E9 = (SELECT
> ADDED_DATE FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E9 = (SELECT
> NUMERIC_VALUE FROM GROUP_9 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E10 =
> (SELECT ENTRY_ID FROM GROUP_10 T WHERE PATIENT_ID = T.PID),
> READ_CODE_E10 =
> (SELECT READ_CODE FROM GROUP_10 T WHERE PATIENT_ID = T.PID),
> TERM_TEXT_E10 =
> (SELECT TERM_TEXT FROM GROUP_10 T WHERE PATIENT_ID = T.PID),
> START_DATE_E10
> = (SELECT START_DATE FROM GROUP_10 T WHERE 

Re: [sqlite] Limit statement size?

[drh]

"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> Is there any limit on the size of the SQL statements in SQLite?

32-bit integers are used to count things in various places.  I
don't really consider that a limit, but some people do.
See http://www.sqlite.org/cvstrac/tktview?tn=2125
There may be other limits that I do not yet know about because
nobody has yet reached them. 

> Didn't think this would come into play, but have now come across this
> query and wonder if this needs considering:

That query will likely work, though efficiency might become an issue.
More importantly, it seems like it might be hard to maintain.
I would refer the author to
http://www.en.wikipedia.org/wiki/Database_normalization as a
starting point.

> 
> UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM  GROUP_2 T WHERE
> PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM GROUP_2 T WHERE
> PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM GROUP_2 T WHERE
> PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM GROUP_2 T WHERE
> PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM GROUP_2 T WHERE
> PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE FROM GROUP_2 T
> WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE FROM GROUP_3 T
> WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT NUMERIC_VALUE FROM
> GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT ENTRY_ID FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT READ_CODE FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT TERM_TEXT FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT START_DATE FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT ADDED_DATE FROM
> GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT
> NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 =
> (SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID), READ_CODE_E5 =
> (SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E5 =
> (SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID), START_DATE_E5 =
> (SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E5 =
> (SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
> NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE PATIENT_ID =
> T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE PATIENT_ID =
> T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE PATIENT_ID =
> T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE PATIENT_ID =
> T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T WHERE PATIENT_ID =
> T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T WHERE PATIENT_ID =
> T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE
> PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM GROUP_7 T WHERE
> PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM GROUP_7 T WHERE
> PATIENT_ID = T.PID), TERM_TEXT_E7 = (SELECT TERM_TEXT FROM GROUP_7 T WHERE
> PATIENT_ID = T.PID), START_DATE_E7 = (SELECT START_DATE FROM GROUP_7 T WHERE
> PATIENT_ID = T.PID), ADDED_DATE_E7 = (SELECT ADDED_DATE FROM GROUP_7 T WHERE
> PATIENT_ID = T.PID), NUMERIC_VALUE_E7 = (SELECT NUMERIC_VALUE FROM GROUP_7 T
> WHERE PATIENT_ID = T.PID), ENTRY_ID_E8 = (SELECT ENTRY_ID FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), READ_CODE_E8 = (SELECT READ_CODE FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), TERM_TEXT_E8 = (SELECT TERM_TEXT FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), START_DATE_E8 = (SELECT START_DATE FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), ADDED_DATE_E8 = (SELECT ADDED_DATE FROM GROUP_8 T
> WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E8 = (SELECT NUMERIC_VALUE FROM
> GROUP_8 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E9 = (SELECT ENTRY_ID FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), READ_CODE_E9 = (SELECT READ_CODE FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E9 = (SELECT TERM_TEXT FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), START_DATE_E9 = (SELECT START_DATE FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E9 = (SELECT ADDED_DATE FROM
> GROUP_9 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E9 = (SELECT
> NUMERIC_VALUE FROM GROUP_9 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E10 =
> (SELECT ENTRY_ID FROM GROUP_10 T WHERE PATIENT_ID = T.PID), READ_CODE_E10 =
> (SELECT READ_CODE FROM GROUP_10 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E10 =
> (SELECT TERM_TEXT FROM GROUP_10 T WHERE PATIENT_ID = T.PID), START_DATE_E10
> = (SELECT START_DATE FROM GROUP_10 T WHERE PATIENT_ID = T.PID),
> ADDED_DATE_E10 = (SELECT ADDED_DATE FROM GROUP_10 T WHERE PATIENT_ID =
> T.PID), NUMERIC_VALUE_E10 = (SELECT NUMERIC_VALUE FROM GROUP_10 T WHERE

[sqlite] Limit statement size?

[RB Smissaert]

Is there any limit on the size of the SQL statements in SQLite?
Didn't think this would come into play, but have now come across this
query and wonder if this needs considering:

UPDATE A3Test115_J SET ENTRY_ID_E2 = (SELECT ENTRY_ID FROM  GROUP_2 T WHERE
PATIENT_ID = T.PID), READ_CODE_E2 = (SELECT READ_CODE FROM GROUP_2 T WHERE
PATIENT_ID = T.PID), TERM_TEXT_E2 = (SELECT TERM_TEXT FROM GROUP_2 T WHERE
PATIENT_ID = T.PID), START_DATE_E2 = (SELECT START_DATE FROM GROUP_2 T WHERE
PATIENT_ID = T.PID), ADDED_DATE_E2 = (SELECT ADDED_DATE FROM GROUP_2 T WHERE
PATIENT_ID = T.PID), NUMERIC_VALUE_E2 = (SELECT NUMERIC_VALUE FROM GROUP_2 T
WHERE PATIENT_ID = T.PID), ENTRY_ID_E3 = (SELECT ENTRY_ID FROM GROUP_3 T
WHERE PATIENT_ID = T.PID), READ_CODE_E3 = (SELECT READ_CODE FROM GROUP_3 T
WHERE PATIENT_ID = T.PID), TERM_TEXT_E3 = (SELECT TERM_TEXT FROM GROUP_3 T
WHERE PATIENT_ID = T.PID), START_DATE_E3 = (SELECT START_DATE FROM GROUP_3 T
WHERE PATIENT_ID = T.PID), ADDED_DATE_E3 = (SELECT ADDED_DATE FROM GROUP_3 T
WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E3 = (SELECT NUMERIC_VALUE FROM
GROUP_3 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E4 = (SELECT ENTRY_ID FROM
GROUP_4 T WHERE PATIENT_ID = T.PID), READ_CODE_E4 = (SELECT READ_CODE FROM
GROUP_4 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E4 = (SELECT TERM_TEXT FROM
GROUP_4 T WHERE PATIENT_ID = T.PID), START_DATE_E4 = (SELECT START_DATE FROM
GROUP_4 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E4 = (SELECT ADDED_DATE FROM
GROUP_4 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E4 = (SELECT
NUMERIC_VALUE FROM GROUP_4 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E5 =
(SELECT ENTRY_ID FROM GROUP_5 T WHERE PATIENT_ID = T.PID), READ_CODE_E5 =
(SELECT READ_CODE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E5 =
(SELECT TERM_TEXT FROM GROUP_5 T WHERE PATIENT_ID = T.PID), START_DATE_E5 =
(SELECT START_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E5 =
(SELECT ADDED_DATE FROM GROUP_5 T WHERE PATIENT_ID = T.PID),
NUMERIC_VALUE_E5 = (SELECT NUMERIC_VALUE FROM GROUP_5 T WHERE PATIENT_ID =
T.PID), ENTRY_ID_E6 = (SELECT ENTRY_ID FROM GROUP_6 T WHERE PATIENT_ID =
T.PID), READ_CODE_E6 = (SELECT READ_CODE FROM GROUP_6 T WHERE PATIENT_ID =
T.PID), TERM_TEXT_E6 = (SELECT TERM_TEXT FROM GROUP_6 T WHERE PATIENT_ID =
T.PID), START_DATE_E6 = (SELECT START_DATE FROM GROUP_6 T WHERE PATIENT_ID =
T.PID), ADDED_DATE_E6 = (SELECT ADDED_DATE FROM GROUP_6 T WHERE PATIENT_ID =
T.PID), NUMERIC_VALUE_E6 = (SELECT NUMERIC_VALUE FROM GROUP_6 T WHERE
PATIENT_ID = T.PID), ENTRY_ID_E7 = (SELECT ENTRY_ID FROM GROUP_7 T WHERE
PATIENT_ID = T.PID), READ_CODE_E7 = (SELECT READ_CODE FROM GROUP_7 T WHERE
PATIENT_ID = T.PID), TERM_TEXT_E7 = (SELECT TERM_TEXT FROM GROUP_7 T WHERE
PATIENT_ID = T.PID), START_DATE_E7 = (SELECT START_DATE FROM GROUP_7 T WHERE
PATIENT_ID = T.PID), ADDED_DATE_E7 = (SELECT ADDED_DATE FROM GROUP_7 T WHERE
PATIENT_ID = T.PID), NUMERIC_VALUE_E7 = (SELECT NUMERIC_VALUE FROM GROUP_7 T
WHERE PATIENT_ID = T.PID), ENTRY_ID_E8 = (SELECT ENTRY_ID FROM GROUP_8 T
WHERE PATIENT_ID = T.PID), READ_CODE_E8 = (SELECT READ_CODE FROM GROUP_8 T
WHERE PATIENT_ID = T.PID), TERM_TEXT_E8 = (SELECT TERM_TEXT FROM GROUP_8 T
WHERE PATIENT_ID = T.PID), START_DATE_E8 = (SELECT START_DATE FROM GROUP_8 T
WHERE PATIENT_ID = T.PID), ADDED_DATE_E8 = (SELECT ADDED_DATE FROM GROUP_8 T
WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E8 = (SELECT NUMERIC_VALUE FROM
GROUP_8 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E9 = (SELECT ENTRY_ID FROM
GROUP_9 T WHERE PATIENT_ID = T.PID), READ_CODE_E9 = (SELECT READ_CODE FROM
GROUP_9 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E9 = (SELECT TERM_TEXT FROM
GROUP_9 T WHERE PATIENT_ID = T.PID), START_DATE_E9 = (SELECT START_DATE FROM
GROUP_9 T WHERE PATIENT_ID = T.PID), ADDED_DATE_E9 = (SELECT ADDED_DATE FROM
GROUP_9 T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E9 = (SELECT
NUMERIC_VALUE FROM GROUP_9 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E10 =
(SELECT ENTRY_ID FROM GROUP_10 T WHERE PATIENT_ID = T.PID), READ_CODE_E10 =
(SELECT READ_CODE FROM GROUP_10 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E10 =
(SELECT TERM_TEXT FROM GROUP_10 T WHERE PATIENT_ID = T.PID), START_DATE_E10
= (SELECT START_DATE FROM GROUP_10 T WHERE PATIENT_ID = T.PID),
ADDED_DATE_E10 = (SELECT ADDED_DATE FROM GROUP_10 T WHERE PATIENT_ID =
T.PID), NUMERIC_VALUE_E10 = (SELECT NUMERIC_VALUE FROM GROUP_10 T WHERE
PATIENT_ID = T.PID), ENTRY_ID_E11 = (SELECT ENTRY_ID FROM GROUP_11 T WHERE
PATIENT_ID = T.PID), READ_CODE_E11 = (SELECT READ_CODE FROM GROUP_11 T WHERE
PATIENT_ID = T.PID), TERM_TEXT_E11 = (SELECT TERM_TEXT FROM GROUP_11 T WHERE
PATIENT_ID = T.PID), START_DATE_E11 = (SELECT START_DATE FROM GROUP_11 T
WHERE PATIENT_ID = T.PID), ADDED_DATE_E11 = (SELECT ADDED_DATE FROM GROUP_11
T WHERE PATIENT_ID = T.PID), NUMERIC_VALUE_E11 = (SELECT NUMERIC_VALUE FROM
GROUP_11 T WHERE PATIENT_ID = T.PID), ENTRY_ID_E12 = (SELECT ENTRY_ID FROM
GROUP_12 T WHERE PATIENT_ID = T.PID), READ_CODE_E12 = (SELECT READ_CODE FROM
GROUP_12 T WHERE PATIENT_ID = T.PID), TERM_TEXT_E12 = 

[sqlite] Re: Re: Re: An SQL question (Not directly related to SQLite)

[Igor Tandetnik]

[EMAIL PROTECTED] wrote:

So I could just use
SELECT ..., SUM(`downloadCount`) FROM ... WHERE `pid` = (SELECT `id`
FROM ...) GROUP
BY `price`;
Is that going to do the same as
SELECT SUM(`downloadCount` FROM (SELECT ... FROM ... WHERE `pid` =
(SELECT ...)) GROUP BY `price`;
?


Yes, these two will produce the same result, but the first query is 
probably more efficient.


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Re: An SQL question (Not directly related to SQLite)

[ivailo91]

On Saturday, January 27, 2007, 10:10:27 PM, Igor 
Tandetnik wrote:
> [EMAIL PROTECTED] wrote:
>> Actually, my query is something like
>> SELECT ... FROM ... WHERE `pid` = (SELECT `id` FROM ...);
>> if i put that group by... will it group all rows, or only those with
>> the same pid?

> GROUP BY works on whatever rows remain after WHERE test.

> Igor Tandetnik

> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -

So I could just use
SELECT ..., SUM(`downloadCount`) FROM ... WHERE `pid` = (SELECT `id` FROM ...) 
GROUP
BY `price`;
Is that going to do the same as
SELECT SUM(`downloadCount` FROM (SELECT ... FROM ... WHERE `pid` =
(SELECT ...)) GROUP BY `price`;
?

Best Regards,
Ivailo Karamanolev


-
To unsubscribe, send email to [EMAIL PROTECTED]
-