Solved

updating one table in sql  via a query referencing two tables

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

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.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

863 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

18 Experts available now in Live!

Get 1:1 Help Now