[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

updating one table in sql  via a query referencing two tables

Posted on 2011-03-01
2
Medium Priority
?
344 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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 …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

591 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