Thanks for your help! It worked like a charm of course...it would have been
nice if I actually read your first post more carefully ;-) tc Stuart
"MGFoster" <me@privacy.com> wrote in message
news:kXClb.1292$wc3.225@newsread3.news.pas.earthli nk.net...[color=blue]
> -----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:
>[color=green]
> > Thank you very much for your reply. I tested it on the sample data I[/color][/color]
posted[color=blue][color=green]
> > and it worked great. Then I tried it one the actual tables. It did not[/color][/color]
work.[color=blue][color=green]
> > 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
> >
wugalter@usc.edu
> >
> >
> > "MGFoster" <me@privacy.com> wrote in message
> > news:__jlb.497$wc3.328@newsread3.news.pas.earthlin k.net...
> >[color=darkred]
> >>-----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[/color]
> >
> > values
> >[color=darkred]
> >>>in table two. The end result should be that Table 1 looks like Table 2.[/color]
> >
> > The
> >[color=darkred]
> >>>changes must be made for each matching ID between the two tables.
> >>>
> >>>TIA
> >>>
> >>>Stuart E. Wugalter
> >>>wugalter@usc.edu
> >>>
> >>>
> >>[/color]
> >
> >[/color]
>[/color]
