Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 74
  • Last Modified:

Access question - SELECT/UPDATE

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
HLRosenberger
Asked:
HLRosenberger
  • 6
  • 4
2 Solutions
 
Kelvin SparksCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
HLRosenbergerAuthor Commented:
that gives me an error.

  SQL error
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Pawan KumarDatabase ExpertCommented:
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
 
HLRosenbergerAuthor Commented:
that too gives me the same syntax error.
0
 
HLRosenbergerAuthor Commented:
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
 
HLRosenbergerAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
Pawan KumarDatabase ExpertCommented:
Great !!!
0
 
HLRosenbergerAuthor Commented:
Thanks!
0
 
HLRosenbergerAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now