Update Query - update fields from one table to another, possible primary key problem

[hinze]

Hi, this is my first ever post, so go easy on me...

I want to update some fields from table 'cases' to some fields in another table 'casesandcontrols'. For example I want to add the PSA data from the PSA field in the 'cases' table to the 1PSA field in the 'casesandcontrols' table, and the data must match up to the corresponding ID. I tried to use an update query to do this but it didn't like how the tables had a primary key, so I removed the primary key from the tables and now it inserts the PSA data into new records and not into the corresponding ID records... any suggestions? My SQL statement is below. Many thanks for your help. Kim

UPDATE [casesandcontrols] RIGHT JOIN [cases] ON ([casesandcontrols].[1PSA] = [cases].PSA) AND ([casesandcontrols].ID = [cases].ID) SET [casesandcontrols].[1PSA] = [cases].PSA
WHERE ((([casesandcontrols].[1PSA]) Is Null));

[kepston]

Hi, this is my first ever post, so go easy on me...

I want to update some fields from table 'cases' to some fields in another table 'casesandcontrols'. For example I want to add the PSA data from the PSA field in the 'cases' table to the 1PSA field in the 'casesandcontrols' table, and the data must match up to the corresponding ID. I tried to use an update query to do this but it didn't like how the tables had a primary key, so I removed the primary key from the tables and now it inserts the PSA data into new records and not into the corresponding ID records... any suggestions? My SQL statement is below. Many thanks for your help. Kim

UPDATE [casesandcontrols] RIGHT JOIN [cases] ON ([casesandcontrols].[1PSA] = [cases].PSA) AND ([casesandcontrols].ID = [cases].ID) SET [casesandcontrols].[1PSA] = [cases].PSA
WHERE ((([casesandcontrols].[1PSA]) Is Null));

Your SQL is trying to JOIN two tables on a field (1PSA) that you are looking for Null in the WHERE clause.
What it is essentially saying is:

Where PSA equals 1PSA set 1PSA to be the same as PSA but only when 1PSA is empty

The following may be of more use

UPDATE [casesandcontrols] RIGHT JOIN [cases] ON ([casesandcontrols].ID = [cases].ID) SET [casesandcontrols].[1PSA] = [cases].PSA
WHERE ((([casesandcontrols].[1PSA]) Is Null));