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

danielolorenz

Asked: 2011-09-08

Advertise Here

Microsoft SQL Server 2008

1 Solution

LVL 32

Ephraim WangoyaCommented: 2011-09-08

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

danielolorenzAuthor Commented: 2011-09-09

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

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.

Expand your skills with this month's free Premium course.

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

Join & Write a Comment Already a member? Login.

Featured Post