Search and Update - Difficult update query

I have 162 tables in my database. Names of the Tables are 1, 2, ......
so on till 162. Every table has only one field (field name = Expr2) of
type NUMBER (DOUBLE) with 352716 records in each table.

I have one more table with 163 fields. Name of the table is "Verify"
which has 701 records. First field (field name="Main" of data type
"BYTE") of Verify has serial numbers from 55 to 755. Names of the rest
of the fields are 1, 2, 3, ..... so on till 162. All these remaining
fields (except "Main") are blank and are of NUMBER (INTEGER) type.

Here is what I want:

The Query/SQL should look the first record (number 55) from the "Main"
field of "Verify" table in first field (Expr2) of the first table (1)
and then update field 1 (field name = 1) of verify with the count
(number of times the number 55 appeared in table 1) continue this till
701 records.

Continue the same thing with all the remaining fields till 162


1 2 3 4 5 .... 162 <=Table names
Expr2 Expr2 Expr2 Expr2 Expr2 Expr2 <=Field name
1 1 1 1 1 1 <=Records
2 2 2 2 2 2
.. . . . . .
.. . . . . .
.. . . . . .
352716 352716 352716 352716 352716 352716 <=EOF

(These 352716 records per field per table are random numbers from 55 to
755)

Verify <=Table name
Main 1 2 3 .... 162 <=Field names
55 _ _ _ _
56 _ _ _ _
57 _ _ _ _
.. _ _ _ _
.. _ _ _ _
.. _ _ _ _
755 _ _ _ _


Not sure if I have explained the scenario properly. Please help...

Thanx
Maxi

re: Search and Update - Difficult update query

Somehow the font is not working for this post.

Please view the later half of my post in courier new font. I have kind
of explained my scenario in a text-pictorial representation.

Maxi

re: Search and Update - Difficult update query

Maxi wrote:
[color=blue]
> I have 162 tables in my database. Names of the Tables are 1, 2, ......
> so on till 162. Every table has only one field (field name = Expr2) of
> type NUMBER (DOUBLE) with 352716 records in each table.[/color]

No offence intended, but why on EARTH would you have this many tables
with just one field????

It would be folly to try and help you do something that on the surface
sounds like a ridiculous arrangement.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

re: Search and Update - Difficult update query

Makes sense. Can this be done if I have only one Table with 162 fields,
and import all fields from the 162 tables.

Maxi

re: Search and Update - Difficult update query

Maxi wrote:
[color=blue]
> .... First field (field name="Main" of data type
> "BYTE") of Verify has serial numbers from 55 to 755.[/color]

Surely this is of the "Big Chomp" data type?

--
--
Lyle
--

re: Search and Update - Difficult update query

You need to do some basic thinking on your data structure. I can think of
no well designed relational database that has 100+ tables and/or 100+ fields
in any table. Have you perhaps confused fields with records?

For example, to record a client's monthly payments, you would NOT have a
table for each client and/or each month/year; you WOULD have one payment
table with 10 or less fields - paymentID, clientID, paymentDate, amount,
etc. This simple structure will handle thousands of payments for thousands
of clients.
-Ed

"Maxi" <mac_mahesh@hotmail.com> wrote in message
news:1108057505.931220.88450@z14g2000cwz.googlegro ups.com...[color=blue]
>I have 162 tables in my database. Names of the Tables are 1, 2, ......
> so on till 162. Every table has only one field (field name = Expr2) of
> type NUMBER (DOUBLE) with 352716 records in each table.
>
> I have one more table with 163 fields. Name of the table is "Verify"
> which has 701 records. First field (field name="Main" of data type
> "BYTE") of Verify has serial numbers from 55 to 755. Names of the rest
> of the fields are 1, 2, 3, ..... so on till 162. All these remaining
> fields (except "Main") are blank and are of NUMBER (INTEGER) type.
>
> Here is what I want:
>
> The Query/SQL should look the first record (number 55) from the "Main"
> field of "Verify" table in first field (Expr2) of the first table (1)
> and then update field 1 (field name = 1) of verify with the count
> (number of times the number 55 appeared in table 1) continue this till
> 701 records.
>
> Continue the same thing with all the remaining fields till 162
>
>
> 1 2 3 4 5 .... 162 <=Table names
> Expr2 Expr2 Expr2 Expr2 Expr2 Expr2 <=Field name
> 1 1 1 1 1 1 <=Records
> 2 2 2 2 2 2
> . . . . . .
> . . . . . .
> . . . . . .
> 352716 352716 352716 352716 352716 352716 <=EOF
>
> (These 352716 records per field per table are random numbers from 55 to
> 755)
>
> Verify <=Table name
> Main 1 2 3 .... 162 <=Field names
> 55 _ _ _ _
> 56 _ _ _ _
> 57 _ _ _ _
> . _ _ _ _
> . _ _ _ _
> . _ _ _ _
> 755 _ _ _ _
>
>
> Not sure if I have explained the scenario properly. Please help...
>
> Thanx
> Maxi
>[/color]