Update Table 1 Certain Fields based on Table 2

[rehanemis]

Hello experts,

I would like to achieve the following task described below:
Just for an example i have two tables = Table1 and Table2

Table1 contains following fields: ID , CusName, Price , Date_
Table2 Contains following fields: ID, CusName, Price, Date_

I would like to update Price and Date Field of Table1 where Table1 ID matching with Table2 and Table2 Date is maximum(most recent date).

Hope this explanation is clear understandable.

Waiting of your response.

Rehan

 

[Frothingslosh]

Having two tables with identical structure means you have normalization errors that will do nothing but make your life more and more difficult over the lifetime of this project.

What precisely are Table1 and Table2, and why are they separate tables? Normally, you have all that data in one table, along with an additional field indicating whatever the difference is.

Example, instead of having two tables called 'Orders Pending' and 'Orders Shipped', you make one 'Orders' table and add a status field that covers, among other things, 'Pending' and 'Shipped'.

 

[rehanemis]

I understand it well dear, but I need to solve this problem. Have you idea about the stated problem.

 

[JANR]

Look into Update query with an Inner Join on you ID field on both tables.

something like this perhaps:

Update Table1 Inner Join Table2 On table1.ID=Table2.ID 
Set table1.Price=table2.price, Table1.[Date_]=Table2[Date_];

Btw a fieldname like Date_ not a good idea, just saying.

JanR

 

[rehanemis]

Thanks for your reply.

I have achieved the task but using another method.

The method you have described is not full-filling my requirements because i only want to update table1 if there is max date in Table2.