By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,608 Members | 1,940 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,608 IT Pros & Developers. It's quick & easy.

Search and Update - Difficult update query

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
Maxi wrote:
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.


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
Nov 13 '05 #3

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

Maxi

Nov 13 '05 #4

P: n/a
Maxi wrote:
.... First field (field name="Main" of data type
"BYTE") of Verify has serial numbers from 55 to 755.


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

--
--
Lyle
--
Nov 13 '05 #5

P: n/a
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" <ma********@hotmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
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

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.