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

Query to Replace Columns

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


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


Nov 12 '05 #2

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

Nov 12 '05 #3

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



Nov 12 '05 #4

P: n/a
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:kX****************@newsread3.news.pas.earthli nk.net...
-----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


Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.