Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

updating one table in sql  via a query referencing two tables

Posted on 2011-03-01
2
Medium Priority
?
342 Views
Last Modified: 2012-05-11

i have the following query:


select a.opp_id, a.no_id, a.AddDate, a.contract, a.TerritoryName,
b.opp_id, b.reference_id, b.flag, b.actdate
from table1  a, table2  b where a.TerritoryName in
(
'21111',
'22222',
'23333',)
and a.AddDate >= '2009-11-01 15:00:55.000'
and a.Opp_ID = b.opp_id


I want to update the b.actdate = '2011-04-05 01:00:55.000'
in the query results above.


would it be

--update table2
set actdate = '2011-04-05 01:00:55.000'
where (select a.opp_id, a.no_id, a.AddDate, a.contract, a.TerritoryName,
b.opp_id, b.reference_id, b.flag, b.actdate
from table1  a, table2  b where a.TerritoryName in
(
'21111',
'22222',
'23333',)
and a.AddDate >= '2009-11-01 15:00:55.000'
and a.Opp_ID = b.opp_id

not sure I have update syntax right



update
0
Comment
Question by:Amanda Walshaw
2 Comments
 
LVL 22

Accepted Solution

by:
Thomasian earned 2000 total points
ID: 35014142
UPDATE b
set actdate = '2011-04-05 01:00:55.000'
from table2  b inner join table1  a on a.Opp_ID = b.opp_id
where a.TerritoryName in 
(
'21111',
'22222',
'23333')
and a.AddDate >= '2009-11-01 15:00:55.000'

Open in new window

0
 

Author Closing Comment

by:Amanda Walshaw
ID: 35014532
yes exact
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

926 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