Query to Replace Columns

[Stuart E. Wugalter]

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
wugalter@usc.ed u

[MGFoster]

Re: Query to Replace Columns

-----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/AwUBP5XMPoechKq OuFEgEQKKzwCgnW lnMntPNz2eITzHj L5k5cA9tG0An0Pi
bwAGsGO+DNF4uU1 XFTx/vvrS
=86cv
-----END PGP SIGNATURE-----


Stuart E. Wugalter wrote:[color=blue]
> 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
> wugalter@usc.ed u
>
>[/color]

[Stuart E. Wugalter]

Re: Query to Replace Columns

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
wugalter@usc.ed u


"MGFoster" <me@privacy.com > wrote in message
news:__jlb.497$ wc3.328@newsrea d3.news.pas.ear thlink.net...[color=blue]
> -----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/AwUBP5XMPoechKq OuFEgEQKKzwCgnW lnMntPNz2eITzHj L5k5cA9tG0An0Pi
> bwAGsGO+DNF4uU1 XFTx/vvrS
> =86cv
> -----END PGP SIGNATURE-----
>
>
> Stuart E. Wugalter wrote:[color=green]
> > 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][/color]
values[color=blue][color=green]
> > in table two. The end result should be that Table 1 looks like Table 2.[/color][/color]
The[color=blue][color=green]
> > changes must be made for each matching ID between the two tables.
> >
> > TIA
> >
> > Stuart E. Wugalter
> > wugalter@usc.ed u
> >
> >[/color]
>[/color]

[MGFoster]

Re: Query to Replace Columns

-----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/AwUBP5b7WIechKq OuFEgEQIM9wCeNo O6S0Egh8tNaly2r y0K+6qCYw4AoNpH
dWdx09xQZAdVnrD VRM/q3jlo
=OIxN
-----END PGP SIGNATURE-----

Stuart E. Wugalter wrote:
[color=blue]
> 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
> wugalter@usc.ed u
>
>
> "MGFoster" <me@privacy.com > wrote in message
> news:__jlb.497$ wc3.328@newsrea d3.news.pas.ear thlink.net...
>[color=green]
>>-----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:::mg f
>>Oakland, CA (USA)
>>
>>-----BEGIN PGP SIGNATURE-----
>>Version: PGP for Personal Privacy 5.0
>>Charset: noconv
>>
>>iQA/AwUBP5XMPoechKq OuFEgEQKKzwCgnW lnMntPNz2eITzHj L5k5cA9tG0An0Pi
>>bwAGsGO+DNF4u U1XFTx/vvrS
>>=86cv
>>-----END PGP SIGNATURE-----
>>
>>
>>Stuart E. Wugalter wrote:
>>[color=darkred]
>>>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][/color]
>
> values
>[color=green][color=darkred]
>>>in table two. The end result should be that Table 1 looks like Table 2.[/color][/color]
>
> The
>[color=green][color=darkred]
>>>changes must be made for each matching ID between the two tables.
>>>
>>>TIA
>>>
>>>Stuart E. Wugalter
>>>wugalter@usc .edu
>>>
>>>[/color]
>>[/color]
>
>[/color]

[Stuart E. Wugalter]

Re: Query to Replace Columns

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@newsre ad3.news.pas.ea rthlink.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/AwUBP5b7WIechKq OuFEgEQIM9wCeNo O6S0Egh8tNaly2r y0K+6qCYw4AoNpH
> dWdx09xQZAdVnrD VRM/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.ed u
> >
> >
> > "MGFoster" <me@privacy.com > wrote in message
> > news:__jlb.497$ wc3.328@newsrea d3.news.pas.ear thlink.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:::mg f
> >>Oakland, CA (USA)
> >>
> >>-----BEGIN PGP SIGNATURE-----
> >>Version: PGP for Personal Privacy 5.0
> >>Charset: noconv
> >>
> >>iQA/AwUBP5XMPoechKq OuFEgEQKKzwCgnW lnMntPNz2eITzHj L5k5cA9tG0An0Pi
> >>bwAGsGO+DNF4u U1XFTx/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]