Solved

Access question - SELECT/UPDATE

Posted on 2016-10-05
11
37 Views
Last Modified: 2016-10-06
How can I do this using Access SQL:

update work_order set local_service_order_id = (select local_Id from service_order where id = 7525) where  service_order_id = 7525
0
Comment
Question by:HLRosenberger
  • 6
  • 4
11 Comments
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 250 total points
ID: 41830837
UPDATE work_order, service_order set local_service_order_id = Service_Order.Local_ID
WHERE work_Order.service_order_id = 7525 and service_order.id = 7525

Kelvin
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41830921
UPDATE w
set w.local_service_order_id = so.Local_ID 
FROM work_order w
INNER JOIN service_order so ON so.Local_ID = w.local_service_order_id
WHERE w.service_order_id = 7525 and so.id = 7525

Open in new window

0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41831628
that gives me an error.

  SQL error
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41831631
Please try this..

UPDATE work_order
set work_order.local_service_order_id = service_order.Local_ID
FROM work_order
INNER JOIN service_order ON service_order.Local_ID = work_order.local_service_order_id
WHERE work_order.service_order_id = 7525 and service_order.id = 7525
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41831655
that too gives me the same syntax error.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:HLRosenberger
ID: 41831660
Kelvin - your SQL worked. Now how do I make it generic so that it updates for all values of xxx, where  work_Order.service_order_id = service_order.id

UPDATE work_order, service_order set local_service_order_id = Service_Order.Local_ID
 WHERE work_Order.service_order_id = xxx and service_order.id = xxx
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41831663
ah, this works:

UPDATE work_order, service_order set local_service_order_id = Service_Order.Local_ID
  WHERE work_Order.service_order_id = service_order.id

right?
0
 
LVL 24

Accepted Solution

by:
Pawan Kumar earned 250 total points
ID: 41831664
try this..to update all values.. (Make sure you have backup of the table you are updating.)

UPDATE work_order, service_order set local_service_order_id = Service_Order.Local_ID WHERE work_Order.service_order_id = service_order.id
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41831666
Great !!!
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41831766
Thanks!
0
 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 41831771
Thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now