November 1, 2007 at 5:51 pm
I have a table with all our databases in it which is truncated and repopulated daily. I need to push this data over to another table which is stable and contains additional fields we would like to use to do some backup scripting. I've got the insert and the update scripts working, but not the delete.
Here are the two tables - db_backup_config is the table that should be maintained.
CREATE TABLE [dbo].[Database_Backup_Config](
[Server] [nvarchar](128) NULL,
[DatabaseName] [nvarchar](128) NOT NULL,
[Size] [int] NOT NULL,
[File_Status] [int] NULL,
[Name] [nvarchar](128) NOT NULL,
[Filename] [nvarchar](260) NOT NULL,
[Status] [nvarchar](128) NULL,
[Updateability] [nvarchar](128) NULL,
[User_Access] [nvarchar](128) NULL,
[Recovery] [nvarchar](128) NULL,
[Frequency] [nvarchar](50) NULL
And
CREATE TABLE [dbo].[Database_Info](
[Server] [nvarchar](128) NOT NULL,
[DatabaseName] [nvarchar](128) NOT NULL,
[Size] [int] NOT NULL,
[File_Status] [int] NULL,
[Name] [nvarchar](128) NOT NULL,
[Filename] [nvarchar](260) NOT NULL,
[Status] [nvarchar](128) NULL,
[Updateability] [nvarchar](128) NULL,
[User_Access] [nvarchar](128) NULL,
[Recovery] [nvarchar](128) NULL
Both tables have a composite key on Server,DatabaseName,Name.
I've added 2 rows to the first table to simulate a database being removed and no longer being present in the daily load.
I've got the subquery with the right outer join working and it returns the proper rows, but when the exist is added (with the correlating join) it deletes all rows. Anyone know why? Is there a better method to do this perhaps?
delete from [DBA_REP].[dbo].[Database_Backup_Config]
where exists
(SELECT i.*,
g.*
FROM [DBA_REP].[dbo].[Database_Info] i right outer join [DBA_REP].[dbo].[Database_Backup_Config] g
on
g.DatabaseName = i.DatabaseName and
g.Server = i.Server and
g.Name = i.Name --and
--[Database_Backup_Config].DatabaseName = g.DatabaseName and
--[Database_Backup_Config].Server = g.Server and
--[Database_Backup_Config].Name = g.Name
where i.DatabaseName is null)
November 2, 2007 at 1:38 am
EXISTS is probably returning a true.
So it is the same as
DELETE FROM TABLE WHERE 1 = 1
the condition is always true so it will delete every row
Rather do something like this:
delete from [DBA_REP].[dbo].[Database_Backup_Config]
FROM
(SELECT g.DatabaseName, g.Server, g.Name
FROM [DBA_REP].[dbo].[Database_Info] i right outer join [DBA_REP].[dbo].[Database_Backup_Config] g
on
g.DatabaseName = i.DatabaseName and
g.Server = i.Server and
g.Name = i.Name --and
-- [Database_Backup_Config].DatabaseName = g.DatabaseName and
-- [Database_Backup_Config].Server = g.Server and
-- [Database_Backup_Config].Name = g.Name
where i.DatabaseName is null) AS D
where [Database_Backup_Config].DatabaseName = D.DataBaseMane
AND [Database_Backup_Config].Server = D.Server
AND [Database_Backup_Config].Name = D.Name
In the above query you will mach all the entries in the derived table to the ones you want to delete.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply