array field in a query

[longhair]

afternoon all,
any suggestions would be greatly appreciated! i'm not sure this is possible and a search of the forum and faqs has not given me a what i think is a solution.
given the following:
table1
text1
text2
text3
double1

table2
text1
text2
text3
double1
tables are related by text1 and text2.
text3 is a field where there may be an array of values colon delimited, in table1. the array, if there, is dynamic. text3 in table2 could be a single instance of a value in table1 text3.
what i would like to do with an update statement is to multiply table2.double1 by table1.double1 if table2.text1 = table1.text1 and table2.text2 = table2.text2 and if the value in table2.text3 = any of the array values in table1.text3.
is there anyway to do this in a single update statemnt? or do i need to read the recordset of table1, split text3 into a variable and then loop though table2?
thanks in advance.
regards,
longhair

 

[Darrylles]

Longhair,

You need to research relational database design, as you don't seem to have a basic understanding of such.

Here's a good link:

http://r937.com/relational.html

1) You have 2 identically structured tables when there is a need only for one (with an extra field denoting the table type).
E.g. all records from both table1 AND table2 will be held in tblBoth:
[tt]
tblBoth
text1
text2
text3
double1
table_num (either "1" or "2")
[/tt]
2) An array in a single field is not the way to do it, why not add an additional table that holds each item of the array in a seperate record - per array item?
E.g.
[tt]
tblBoth tblText3
text1 ------------->> text1
text2 ------------->> text2
double1 text3 (individual array item)
table_num
[/tt]
(-->> denotes 1-to-many, meaning that tblText3 may hold many text3 records per tblBoth record).

It would be helpful if you can show some example data (and do a little relational db research in the mean-time).

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[lameid]

If you want to test to see if the arrays are equal then no you don't need a record set.

First start with the select statement.

Join on Text1 and Text2.

Use an in statemtne on Table2.text3 and in the body return all the values in table1.text3.

From there is should be a simple matter to change it to an update query.