Update Using Data from Another Table

[jsccorps]

For example:

Table A : ID Title Code Table B: ID Title
Code
01 ABC 01FA 02
DDD 'null'
02 DDD 02JK 55
BBR 045V
07 F9F 117L 16
PIM 'null'
16 PIM 76qe

I want to create an update query that joins the tables based on the ID and
Title, and updates the Code in Table B with the Code values from Table A.
When I try to update the 'cross product' causes problems (duplicates).

 

[MGFoster]

For example:

Table A : ID Title Code Table B: ID Title
Code
01 ABC 01FA 02
DDD 'null'
02 DDD 02JK 55
BBR 045V
07 F9F 117L 16
PIM 'null'
16 PIM 76qe

I want to create an update query that joins the tables based on the ID and
Title, and updates the Code in Table B with the Code values from Table A.
When I try to update the 'cross product' causes problems (duplicates).

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Access way is like this:

UPDATE TableB INNER JOIN TableA ON TableB.ID = TableA.ID And
TableB.Title = TableA.Title
SET TableB.Code = TableA.Code

SQL standard way is like this:

UPDATE TableB
SET Code = (SELECT Code FROM TableA WHERE ID = TableB.ID And Title =
TableB.Title)

Don't look at the datasheet view of these queries - the output may look
redundant. The actual UPDATE only updates the required rows.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSZI/roechKqOuFEgEQKxuACfUFMwIk93KBuknaxHeci51nkyuY8An3ng
Meahdvwjhlqz9Lx3ETMrEQ2q
=CiMP
-----END PGP SIGNATURE-----