Solved

Access question - SELECT/UPDATE

Posted on 2016-10-05
11
44 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 28

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 28

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
 
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 28

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 28

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 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