Solved

updating one table in sql  via a query referencing two tables

Posted on 2011-03-01
2
337 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 500 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 39
sql query to calculate avaerage 21 52
Pivot tables in SQL 1 20
Substring works but need to tweak it 14 16
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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 …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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