Update One Field in a Table from Another

[user9]

I have two tables - Ledger and RWV. Both have the primary key RV. I'm trying to update field "Amount" in RWV from the field "Amount" in Ledger, but change no other fields.



Here's what I have that is not returning anything:

UPDATE Ledger LEFT JOIN RWV ON Ledger.RV = RWV.RV SET RWV.Amount = Ledger.Amount
WHERE (((RWV.RV)=[Ledger].[RV]));

Should be so simple, just need another set of eyes. Thanks in advance!

[lfpm062010]

What are you expect to return? "Update" does not return anything except a count of how many records are updated.

You are also using "LEFT JOIN" instead of "INNER JOIN".
The "LEFT JOIN" with "(((RWV.RV)=[Ledger].[RV]))" is same as just "INNER JOIN".

UPDATE Ledger INNER JOIN RWV ON Ledger.RV = RWV.RV SET RWV.Amount = Ledger.Amount;

[June7]

Why are you duplicating data?

[orange]

Agree with the others --what ae you doing?
Can you step back and tell us in plain English what you are trying to accomplish?

[user9]

Table 1 is the project master with 25 columns - including "RV". Table 2 is new and has 2 columns - "RV" and "Amount". Both have the same Primary Key - "RV".

I have added an "Amount" column to Table 1. Where RV matches on both tables, I need to populate Table 1 with the value from Table 2.

There are no duplicates, "Amount" did not previously exist in Table 1. It's a new field available to add to the master table.

[June7]

The duplication is having Amount in two tables. If both tables are retained then why duplicate Amount into Table1 instead of just using a SELECT query that joins the two tables? Is this UPDATE a one-time event and then Table2 will be elminated? If RV is primary key in both tables then this is a 1-to-1 relationship and really should be one table. If RV is actually a foreign key in Table1 and Table2 is a lookup table then is Amount in Table2 subject to change in the future and you want to commit the current value to Table1 records?

There was no need for the WHERE clause in the original query. Not sure why the LEFT JOIN update failed.

[rpeare]

That's not explaining WHY. You are explaining WHAT you want to do. If your tables have a 1 to 1 relationship you shouldn't need to carry the same piece of information from one table to another.