Need help with an Update Query

[zipaway]

I've been stuck working on this SQL Update query for a while and not getting anywhere; any help would greatly be appreciated.

I have two tables and they have the following relevant fields:



Projects
Project_Number (Primary Key)
Sched_Next_Ship_Date

Ship&Delivery Dates
ID (Primary Key)
Project_Number
Component_Number
Sched_Ship_Date

Ship&Delivery Dates (Sample Table)


A project may have more than one component with different ship dates. The Sched_Next_Ship_Date field should take the upcoming component's Sched_Ship_Date for that project. So for instance project 10135's Sched_Next_Ship_Date would be 27/05/14 since that is the upcoming component out for shipment relative to today's date.

The SQL query below simply adds the last entered Sched_Ship_Date to the projects table (for above example would add 30/05/2014); this is incorrect. I want the 'upcoming' ship date to be added to the projects table. The 'upcoming' ship date would be the date that is coming up next relative to today's date. I think CURDATE and a WHERE clause need to be added to the end of this query to make it work like I want it to but I don't know how to do it. Let me know if this is possible or not.


Query so far:

UPDATE Projects INNER JOIN [Ship&Delivery Dates] ON Projects.Project_Number = [Ship&Delivery Dates].Project_Number SET Projects.Sched_Next_Ship_Date = [Ship&Delivery Dates].[Sched_Ship_Date]

[June7]

This appears to be saving duplicate data. The Sched_Next_Ship_Date can be retrieved by query whenever needed. It is not necessary to store in another table. Stored or not, it is the same query to retrieve the value. The extra step of committing the value to another table is unnecessary.

You want to retrieve the date that is closest to and later than or equal to the current date? The component number is not a consideration? Try:

SELECT Project_Number, Min(Sched_Ship_Date) AS Sched_Next_Ship_Date FROM ShipDeliveryDates WHERE Sched_Ship_Date>=Date() GROUP BY Project_Number;


BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[zipaway]

This appears to be saving duplicate data. The Sched_Next_Ship_Date can be retrieved by query whenever needed. It is not necessary to store in another table. Stored or not, it is the same query to retrieve the value. The extra step of committing the value to another table is unnecessary.

You want to retrieve the date that is closest to and later than or equal to the current date? The component number is not a consideration? Try:

SELECT Project_Number, Min(Sched_Ship_Date) AS Sched_Next_Ship_Date FROM ShipDeliveryDates WHERE Sched_Ship_Date>=Date() GROUP BY Project_Number;


BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

Wow this makes a lot of sense, thank you so much!

And yeah I will change the naming convention.