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)
)
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'
LEFT OUTER JOIN tblInstance ON tblDatabase.Instance_id = tblInstance.Instance_id
WHERE tblInstance.Server_Name NOT IN
try
Open in new window