Update query not updating the full field

Question

I have two tables, Actual Use and Budget. I need to update my [Actual Use].Goals with my monthly Budget.goals. I have an update query:

UPDATE [Actual Use]
INNER JOIN Budget ON [Actual Use].Property_ID = Budget.Property_ID
SET
    [Actual Use].Goal = [Budget].Goal
WHERE
    [Actual Use].Date = [Budget].Date

This query updates my Actual Use table but only for one month. 1/1/2016. Both Actual Use and Budget have a date field and all dates are entered with the first of each month so 1/1/2016, 2/1/2016 etc... Why is my update only working on one month and not every month where the Property_ID and month are the same on both tables?

Edit The Acutal Use Table has the following fields in this order Property_Id, Date, Use, Goal and the Budget Table has Property_ID, Date, Goal

It's hard to say without actually seeing your table structures and sample data. stackoverflow.com/help/how-to-ask — Tom H
Are you sure that you have recordings in your db that meet your condition? — Andreea Dumitru

Nate M. Nate M. · Accepted Answer · 2016-02-15T20:53:13

I agree with the comment by Olivier. You need to join on both ID and Date.

Depending on what your table keys are, the above listed query could produce a One to Many or Many to Many result, in which, the program doesn't know which goal to assign. I suppose the Where clause may catch the many to many circumstance, but in my opinion, could produce some weird behavior.

Possible solution:

UPDATE [Actual Use]
INNER JOIN Budget ON [Actual Use].Property_ID = Budget.Property_ID 
AND [Actual Use].Date = Budget.Date
SET
    [Actual Use].Goal = [Budget].Goal

On a side note, it seems like the data is a bit repetitious? Presuming there was no 'goal' in table [Actual Use], you could still reference the data by the following:

SELECT * FROM [Actual Use] au
JOIN Budget b on au.Property_ID = b.Property_ID AND
au.Date = b.Date

Update query not updating the full field

1 Answers