-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
You've created a "cartesian join" query, which means that all possible
combinations of the two tables will be created in the result set
(update set). You forgot to INNER JOIN the two tables on the ID
columns.
Is the column "SNPID" really an updateable column? Wasn't it supposed
to be the ID column you had in your first post? Though it won't
matter if you overwrite the T1 SNPID with T2 SNPID's value ('cuz
they're both supposed to be the same) it would be more efficient to
not overwrite the ID column.
If the SNPID is really the ID column for each table here's my re-write
of your query:
UPDATE [tbl Master Table] AS T1 INNER JOIN NewProbeData AS T2
ON T1.SNPID = T2.SNPID
SET T1.OrderNum = T2.OrderNum,
T1.A1 = T2.A1,
T1.A2 = T2.A2;
If you don't want to use the INNER JOIN syntax you can do the same
thing like this:
UPDATE [tbl Master Table] AS T1, NewProbeData AS T2
SET T1.OrderNum = T2.OrderNum,
T1.A1 = T2.A1,
T1.A2 = T2.A2
WHERE T1.SNPID = T2.SNPID
This was the "old" way to create an inner join.
HTH,
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBP5b7WIechKqOuFEgEQIM9wCeNoO6S0Egh8tNaly2ry0K+6 qCYw4AoNpH
dWdx09xQZAdVnrDVRM/q3jlo
=OIxN
-----END PGP SIGNATURE-----
Stuart E. Wugalter wrote:
Thank you very much for your reply. I tested it on the sample data I posted
and it worked great. Then I tried it one the actual tables. It did not work.
Here is the SQL command I used:
UPDATE [tbl Master Table] AS T1, NewProbeData AS T2 SET T1.OrderNum =
T2.OrderNum, T1.SNPID = T2.SNPID, T1.A1=T2.A1, T1.A2=T2.A2;
tbl Master Table has about 270 records and NewProbeData has about 240
records. The query asked me if I wanted to update 93,000 rows.
What am I doing wrong now? TIA
Stuart E. Wugalter
wu******@usc.edu
"MGFoster" <me@privacy.com> wrote in message
news:__***************@newsread3.news.pas.earthlin k.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
You're replacing text with numbers - is that what you want to do? If
so, do you want to change the data type of the columns, also - from
text to numeric?
To simply replace values (Query design - SQL view):
UPDATE Table1 As T1 INNER JOIN Table2 As T2
ON T1.ID = T2.ID
SET T1.Field1 = T2.Field1,
T1.Field2 = T2.Field2
- --
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBP5XMPoechKqOuFEgEQKKzwCgnWlnMntPNz2eITzHjL5k5c A9tG0An0Pi
bwAGsGO+DNF4uU1XFTx/vvrS
=86cv
-----END PGP SIGNATURE-----
Stuart E. Wugalter wrote:
Here is a sample of what I want to do:
Table 1
ID FIELD1 FIELD2
1 A T
2 G C
3 T C
Table2
ID FIELD1 FIELD2
1 1 4
2 3 2
3 4 3
I want to update the values of FIELD1 and FIELD2 in table1 with the
values
in table two. The end result should be that Table 1 looks like Table 2.
The
changes must be made for each matching ID between the two tables.
TIA
Stuart E. Wugalter
wu******@usc.edu
