UPDATE query with two tables problem

[kerryokie27]

Warehouse shipping application. In the master table, I have Purchase_Order, Planned_Ship_Date, and Actual_Ship_Date. I get a daily update that I load as a staging table which contains updated Planned_Ship_Date and Actual_Ship_Date. Purchase_Order doesn't change, so it becomes the index.I have tried multiple queries to update the master table and can't get it to pick up the revised dates. Query Design yields the following:

UPDATE staging_table
       INNER JOIN
       master_table
    ON (staging_table.[Actual_Ship_Date] = master_table.[Actual_Ship_Date])
   AND (staging_table.[Planned_Ship_Date] = master_table.[Planned_Ship_Date])
SET    staging_table.Planned_Ship_Date = [master_table].[Planned_Ship_date]
     , staging_table.Actual_Ship_Date = [master_table].[Actual_Ship_Date];

The query above doesn't update anything. Do I need a WHERE that ties the UPDATE to Purchase_Order? Any and all help greatly appreciated!

[twinnyfo]

kerryokie27,

Welcome to Bytes!

Your code is telling the query to update the staging table.

I think you want it the other way around?

Hope that hepps!

[NeoPa]

Hi Kerry.

It's hard to know what you expect from this query and what you're actually trying to achieve. Currently, what it is doing is changing the two fields so that the values match between the two tables, but only if they already match.

I suspect it's actually working perfectly - just not doing what you expect it to. Therein lies the problem. Whatever it is you're expecting is certainly not what you've told it to do.

KerryOkie27:
Do I need a WHERE that ties the UPDATE to Purchase_Order?

Sort of. Actually, you need to rip out your current UPDATE clause and change how the two tables are linked. It's hard to say exactly how they should be linked as you don't say if the [staging_table] contains one or multiple items that match on [Purchase_Order] alone. The following should work if there can only ever be one record in the [staging_table]. If all of them are left in there from one day to the next then it gets more complicated (We'll deal with that if & when we have to) :

UPDATE [master_table] AS [MT]
       INNER JOIN
       [staging_table] AS [ST]
    ON [MT].[Purchase_Order]=[ST].[Purchase_Order]
SET    [MT].[Planned_Ship_Date]=[ST].[Planned_Ship_Date]
     , [MT].[Actual_Ship_Date]=[ST].[Actual_Ship_Date]

[kerryokie27]

Thank you for your reply, NeoPa.

What I need to do is update the master table from the staging table anytime either (or both) of the dates change. The warehouse keeps the dates on an Excel spreadsheet, so I import the Excel sheet to create the staging table. There are typically 25 - 30 purchase orders that go into the staging table each day.

I'll enter your code above and see where it takes me - thanks again!

[NeoPa]

Hi Kerry.

You could include a WHERE clause to restrict the updates to only those records where one or other of the dates have changed, but I guess, from your explanation, that the record would only exist in the staging table if one of them were different anyway.

One thing I'm still not clear about - Does the spreadsheet, and thus [staging_table], have the full history of updates or will it only contain those for today? It's important because if historical records are in there too then your SQL would need to be updated to handle that. It would run without the update but the results would be unpredictable.

[kerryokie27]

Hi NeoPa,

What I'm currently doing is using Spreadsheet Compare (comes bundled with Office) and taking the results to build my staging file - so, currently, the only records in the staging file are those known to have changes.

That said, I'd like to eliminate this manual step, so I'd like to see the solution that allows older records to remain in the staging file. Thank you for all your help so far!

[NeoPa]

Hi Kerry.

As things stand, from the information we currently have, we have no way of identifying which records of [staging_table] are relevant. Hopefully there is more information in this table, such as a date or maybe even a date/time value, that can allow us to select one of the records to use. Consider the hypothetical situation where a purchase order has three entries added over a period with three different sets of dates. How would the process recognise which of those three to use?

So far, all we know is that they have different values in [Planned_Ship_Date] & [Actual_Ship_Date] (from each other). We know of nothing (yet) to indicate which of the three is the one we would want to update [master_table] to.

Possibly there is an extra field we could use to identify which record is most appropriate. If so then you'll need to tell us about it. If not then how do you tell manually. Is it a simple case of ignoring records that weren't added most recently? If so then we need some data that reflects that in a way we can utilise. That's possible, but would involve storing a copy of the table as was and processing the compare internally in Access.

In short, we can't advise without all the relevant information from you.