Using an Append or Update Query to insert data into a table (Using 2 tables)

[Sawduster]

Iam try to create an append query, in order to, insert records into an already existing field (using 2 different tables).

Basically, I want to insert the dates from a column (field) labeled “GraduationDate” that is located in a table labeled “OrderHeader” into a table labeled “OrderDeliveryAlt” in a column (field) also labeled “GraduationDate” but only update that field that contains the same “OrderNu” as the date.

For example, everywhere there is the OrderNu “T0020204” with the graduation date of 5/15/2018 (in the OrderHeader table) I want that graduation date to be inserted into the graduation date in the OrderDeliveryAlt table with that corresponding OrderNu.

Here is what I have but it doesn’t work:

INSERT INTO OrderDeliveryAlt ( GraduationDate )
SELECT OrderHeader.GraduationDate
FROM OrderHeader;

Which is the correct SQL code to use in this case? Should I use an append query or Update Query?

 

[Joe4]

You are using the wrong query type.

An Append Query is used to Insert brand new records into a Table.
An Update Query is used to Update fields on existing records in a Table.

It sounds to me like you are updating existing records, so want an Update Query.
Join the two tables on your OrderNu value, then update the Graduation Date field.

See here: https://support.office.com/en-us/ar...te-query-9dddc97c-f17d-43f4-a729-35e5ee1e0514

 

[Sawduster]

Ok I tried this code however it didn't work. How can this be corrected?

UPDATE OrderHeader INNER JOIN OrderDeliveryAlt ON OrderHeader.OrderNu = OrderDeliveryAlt.OrderNu SET OrderDeliveryAlt.GraduationDate = "GraduationDate";

 

[Joe4]

If you are UPDATING the "OrderHeader" table, why is your SET command referencing a field from the "OrderDeliveryAlt" table?
These two tables should be the same.

And why are you trying to set the field equal to the Text string "GraduationDate", instead of the GraduationDate field (anything enclosed in double-quotes is treated as literal text)?

It should be something like:

[COLOR=#333333]UPDATE OrderHeader 
INNER JOIN OrderDeliveryAlt ON OrderHeader.OrderNu = OrderDeliveryAlt.OrderNu 
SET [/COLOR][COLOR=#333333]OrderHeader.[/COLOR][COLOR=#333333]GraduationDate=[/COLOR][COLOR=#333333]OrderDeliveryAlt.GraduationDate;[/COLOR]

 

[xenou]

Graduation Date is a Column name, not a string. Also you are updating the wrong table:

UPDATE 
    [B]OrderDeliveryAlt [/B]
    INNER JOIN OrderHeader 
    ON OrderDeliveryAlt.OrderNu = OrderHeader.OrderNu 
SET 
    OrderDeliveryAlt.[B]GraduationDate [/B]= OrderHeader.[B]GraduationDate[/B];

Also as a rule I'd only update null dates or possibly dates that differ, on the premise that you should never be updating things that don't need to be updated:

UPDATE 
    OrderDeliveryAlt 
    INNER JOIN OrderHeader 
    ON OrderDeliveryAlt.OrderNu = OrderHeader.OrderNu 
SET 
    OrderDeliveryAlt.GraduationDate = OrderHeader.GraduationDate
WHERE
    OrderDeliveryAlt IS NULL;

or

UPDATE 
    OrderDeliveryAlt 
    INNER JOIN OrderHeader 
    ON OrderDeliveryAlt.OrderNu = OrderHeader.OrderNu 
SET 
    OrderDeliveryAlt.GraduationDate = OrderHeader.GraduationDate
WHERE
    Nz(OrderDeliveryAlt.GraduationDate ,#1/1/1970#) <> OrderHeader.GraduationDate;

Although it wouldn't actually hurt to update a value to what it already is, generally speaking, I would still try to avoid this, especially considering this query will essentially be updating all of your records all the time otherwise, and that will degrade performance over time unless it's a one-off action.

Note that I used to believe that in Access the table you are updating must come first in a join clause, but now I really don't know anymore if I was correct or not. Access is a bit more finicky when it comes to updates and deletes than other database engines.