We help IT Professionals succeed at work.

UpdateQuery for Row Update To Another Table

66 Views
Last Modified: 2018-10-04
Gd Day, I am trying to determine if there's a way for a Tbl1 query to run an individual row update to a linked Table2 based on matched row records from the table1 ?
Both Tables are structurally identical, fields data of both Tables "Client", "OrderDate" and "ProductNo" always identical when replicated.
Only Field1 to 3 are constant updating in Tbl1 daily, new client name always enter into Tbl2 only, therefore in this case, delete and append to make up for the update to Tbl 2 is out of the question.

Tbl1                                                                                            Tbl2
Client    OrderDate  ProductNo  Fld1   Fld2    Fld3             Client    OrderDate  ProductNo  Fld1   Fld2    Fld3
Client1  10-01-2018   10-1234A    A       100       X                Client1   10-01-2018   10-1234A                                              
Client2  10-02-2018   25-1857C    B       330       Y                Client2   10-02-2018   25-1857C                                                
Client3  10-03-2018   3320E-A1    C       550       Z                Client3    10-03-2018   3320E-A1                                                
                                                                                                    Client4
                                                                                                    Client5

Tbl2 Result after update query update from tbl1
Client           OrderDate      ProductNo      Fld1       Fld2          Fld3
Client1        1-Oct-2018      10-1234A          A           100             X
Client2        2-Oct-2018      25-1857C          B           330             Y
Client3        3-Oct-2018      3320E-A1          C           550             Z
Client4
Client5


Please note, the row update criteria for records are "Client", "OrderDate" and "ProductNo", Fields pending update in Tbl2 are actually way more then 3 as shown as example
The Update Query must be able to find the match of "Client", "OrderDate" and "ProductNo" in tbl2 to update Field 1 to 3+

Appreciate if anyone able to provide solutions to obtain the result.

Thx
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
It works great, thx
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
You could also have used a merge -
merge into tbl2 t2
using tbl1 t1
on (t1.client = t2.client and t1.orderdate = t2.orderdate and t1.productno = t2.productno)
when matched then
update set
t2.fld1 = t1.fld1,
t2.fld2 = t1.fld2,
t2.fld3 = t1.fld3;
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Not in Access SQL.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.