SHARE
TWEET

Untitled

a guest Jun 20th, 2019 56 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE dbo.InconvenientTable
  2. (
  3.     Id int IDENTITY(1,1) NOT NULL,
  4.     [Description] varchar(10) NOT NULL,
  5.  
  6.     CONSTRAINT PK_InconvenientTable
  7.         PRIMARY KEY (Id)
  8. );
  9. GO
  10.  
  11. CREATE TABLE dbo.MainTable1
  12. (
  13.     Id int IDENTITY(1,1) NOT NULL,
  14.     [Description] varchar(10) NOT NULL,
  15.     InconvenientTableId int NOT NULL,
  16.  
  17.     CONSTRAINT FK_MainTable1_InconvenientTable
  18.         FOREIGN KEY (InconvenientTableId)
  19.         REFERENCES dbo.InconvenientTable (Id)
  20. );
  21.  
  22. CREATE TABLE dbo.MainTable2
  23. (
  24.     Id int IDENTITY(1,1) NOT NULL,
  25.     [Description] varchar(10) NOT NULL,
  26.     InconvenientTableId int NOT NULL,
  27.  
  28.     CONSTRAINT FK_MainTable2_InconvenientTable
  29.         FOREIGN KEY (InconvenientTableId)
  30.         REFERENCES dbo.InconvenientTable (Id)
  31. );
  32.      
  33. INSERT INTO dbo.InconvenientTable
  34.     ([Description])
  35. VALUES
  36.     ('One'),
  37.     ('Two'),
  38.     ('Three');
  39. GO
  40.  
  41. INSERT INTO dbo.MainTable1
  42.     ([Description], InconvenientTableId)
  43. VALUES
  44.     ('One', 1),
  45.     ('Two', 2),
  46.     ('Three', 3);
  47.  
  48. INSERT INTO dbo.MainTable2
  49.     ([Description], InconvenientTableId)
  50. VALUES
  51.     ('Two', 2);
  52.      
  53. CREATE VIEW dbo.InconvenientTable_RowsToDelete
  54. AS
  55. SELECT it.*
  56. FROM dbo.InconvenientTable it
  57. WHERE
  58.     NOT EXISTS (SELECT NULL FROM dbo.MainTable1 mt1 WHERE mt1.InconvenientTableId = it.Id)
  59.     AND NOT EXISTS (SELECT NULL FROM dbo.MainTable2 mt2 WHERE mt2.InconvenientTableId = it.Id);
  60. GO
  61.      
  62. DELETE dbo.MainTable2 WHERE InconvenientTableId = 2;
  63. DELETE dbo.MainTable1 WHERE InconvenientTableId = 2;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top