Improve company productivity with a Business Account.Sign Up

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

How Do I Do an SQL Update From a Field on a Table Join?

I need to update tblDatabase status field to 'P' where tblnstance.Server_Name does not exist in my subquery.  I am getting an error on my table joins.

Code Below:

UPDATE tblDatabase SET tblDatabase.status = 'P'
FROM tblApplication INNER JOIN  
tblDBAppRel ON tblApplication.Application_Id = tblDBAppRel.Application_Id INNER JOIN
tblDatabase ON tblDatabase.Database_Id = tblDBAppRel.Database_Id LEFT OUTER JOIN
tblInstance ON tblDatabase.Instance_id = tblInstance.Instance_id
WHERE tblInstance.Server_Name NOT IN
               (SELECT Server_Name
                     FROM #DBWDAIInventory
                     WHERE Server_Name IN (SELECT Server_Name FROM tblMSSQLExtract)
               )
               
How do you write this query correctly?

Thanks,

Dan
0
danielolorenz
Asked:
danielolorenz
1 Solution
 
Ephraim WangoyaCommented:
Your statement seems a little contradictory

LEFT OUTER JOIN tblInstance ON tblDatabase.Instance_id = tblInstance.Instance_id
WHERE tblInstance.Server_Name NOT IN

try
;with cte as
(
  select tblDatabase.Instance_id, tblDatabase.status
  from tblDatabase 
  INNER JOIN tblDBAppRel ON tblDatabase.Database_Id = tblDBAppRel.Database_Id 
  inner join tblApplication ON tblApplication.Application_Id = tblDBAppRel.Application_Id 
  inner JOIN tblInstance ON tblDatabase.Instance_id = tblInstance.Instance_id
  WHERE tblInstance.Server_Name NOT IN
               (SELECT Server_Name
                     FROM #DBWDAIInventory
                     WHERE Server_Name IN (SELECT Server_Name FROM tblMSSQLExtract)
               )
)

UPDATE cte 
SET [status] = 'P'

Open in new window

0
 
danielolorenzAuthor Commented:
Your solution this time worked great.  With the table join all of the tblDatabase status rows updated successfully and the way I wanted them to.

Thanks for your help.

Dan
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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