Solved

using t-sql EXISTS

Posted on 2016-11-22
8
62 Views
Last Modified: 2016-11-23
EXISTS (T-SQL)
https://msdn.microsoft.com/en-us/library/ms188336.aspx

I have a table called Employees1 which looks like this. I have another table called Employees2 which is basically a copy of Employees1
Employees1
I just created these two tables for this example.

Here is the script to create Employees1
USE [Northwind]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employees1](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [nvarchar](20) NOT NULL,
	[FirstName] [nvarchar](10) NOT NULL,
	[Title] [nvarchar](30) NULL,
	[TitleOfCourtesy] [nvarchar](25) NULL,
	[BirthDate] [datetime] NULL,
	[HireDate] [datetime] NULL,
	[Address] [nvarchar](60) NULL,
	[City] [nvarchar](15) NULL,
	[Region] [nvarchar](15) NULL,
	[PostalCode] [nvarchar](10) NULL,
	[Country] [nvarchar](15) NULL,
	[HomePhone] [nvarchar](24) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employees1] ON
INSERT [dbo].[Employees1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (1, N'Davolio', N'Nancy', N'Sales Representative', N'Ms.', CAST(0x000045D100000000 AS DateTime), CAST(0x000083BB00000000 AS DateTime), N'507 - 20th Ave. E.Apt. 2A', N'Seattle', N'WA', N'98122', N'USA', N'(206) 555-9857')
INSERT [dbo].[Employees1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (2, N'Fuller', N'Andrew', N'Vice President, Sales', N'Dr.', CAST(0x00004A6100000000 AS DateTime), CAST(0x0000842400C5C100 AS DateTime), N'908 W. Capital Way', N'Tacoma', N'WA', N'98401', N'USA', N'(206) 555-9482')
INSERT [dbo].[Employees1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (3, N'Leverling', N'Janet', N'Sales Representative', N'Ms.', CAST(0x00005AD300000000 AS DateTime), CAST(0x0000839D00000000 AS DateTime), N'722 Moss Bay Blvd.', N'Kirkland', N'WA', N'98033', N'USA', N'(206) 555-3412')
INSERT [dbo].[Employees1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (4, N'Peacock', N'Margaret', N'Sales Representative', N'Mrs.', CAST(0x000035CF00000000 AS DateTime), CAST(0x0000852A0083D600 AS DateTime), N'4110 Old Redmond Rd.', N'Redmond', N'WA', N'98052', N'USA', N'(206) 555-8122')
INSERT [dbo].[Employees1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (5, N'Buchanan', N'Steven', N'Sales Manager', N'Mr.', CAST(0x00004EB600000000 AS DateTime), CAST(0x000085D100000000 AS DateTime), N'14 Garrett Hill', N'London', NULL, N'SW1 8JR', N'UK', N'(71) 555-4848')
INSERT [dbo].[Employees1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (6, N'Suyama', N'Michael', N'Sales Representative', N'Mr.', CAST(0x00005A9800000000 AS DateTime), CAST(0x000085D100000000 AS DateTime), N'Coventry House
Miner Rd.', N'London', NULL, N'EC2 7JR', N'UK', N'(71) 555-7773')
INSERT [dbo].[Employees1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (7, N'King', N'Robert', N'Sales Representative', N'Mr.', CAST(0x0000562F00000000 AS DateTime), CAST(0x0000861E00000000 AS DateTime), N'Edgeham Hollow
Winchester Way', N'London', NULL, N'RG1 9SP', N'UK', N'(71) 555-5598')
INSERT [dbo].[Employees1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (8, N'Callahan', N'Laura', N'Inside Sales Coordinator', N'Ms.', CAST(0x000052C800000000 AS DateTime), CAST(0x0000865C00000000 AS DateTime), N'4726 - 11th Ave. N.E.', N'Seattle', N'WA', N'98105', N'USA', N'(206) 555-1189')
INSERT [dbo].[Employees1] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (9, N'Dodsworth', N'Anne', N'Sales Representative', N'Ms.', CAST(0x00005E4400000000 AS DateTime), CAST(0x0000875B00000000 AS DateTime), N'7 Houndstooth Rd.', N'London', NULL, N'WG2 7LT', N'UK', N'(71) 555-4444')
SET IDENTITY_INSERT [dbo].[Employees1] OFF

Open in new window



Here is the script to create Employees2

USE [Northwind]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employees2](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [nvarchar](20) NOT NULL,
	[FirstName] [nvarchar](10) NOT NULL,
	[Title] [nvarchar](30) NULL,
	[TitleOfCourtesy] [nvarchar](25) NULL,
	[BirthDate] [datetime] NULL,
	[HireDate] [datetime] NULL,
	[Address] [nvarchar](60) NULL,
	[City] [nvarchar](15) NULL,
	[Region] [nvarchar](15) NULL,
	[PostalCode] [nvarchar](10) NULL,
	[Country] [nvarchar](15) NULL,
	[HomePhone] [nvarchar](24) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employees2] ON
INSERT [dbo].[Employees2] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (1, N'Davolio', N'Nancy', N'Sales Representative', N'Ms.', CAST(0x000045D100000000 AS DateTime), CAST(0x000083BB00000000 AS DateTime), N'507 - 20th Ave. E.Apt. 2A', N'Seattle', N'WA', N'98122', N'USA', N'(206) 555-9857')
INSERT [dbo].[Employees2] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (2, N'Fuller', N'Andrew', N'Vice President, Sales', N'Dr.', CAST(0x00004A6100000000 AS DateTime), CAST(0x0000842400C5C100 AS DateTime), N'908 W. Capital Way', N'Tacoma', N'WA', N'98401', N'USA', N'(206) 555-9482')
INSERT [dbo].[Employees2] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (3, N'Leverling', N'Janet', N'Sales Representative', N'Ms.', CAST(0x00005AD300000000 AS DateTime), CAST(0x0000839D00000000 AS DateTime), N'722 Moss Bay Blvd.', N'Kirkland', N'WA', N'98033', N'USA', N'(206) 555-3412')
INSERT [dbo].[Employees2] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (4, N'Peacock', N'Margaret', N'Sales Representative', N'Mrs.', CAST(0x000035CF00000000 AS DateTime), CAST(0x0000852A0083D600 AS DateTime), N'4110 Old Redmond Rd.', N'Redmond', N'WA', N'98052', N'USA', N'(206) 555-8122')
INSERT [dbo].[Employees2] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (5, N'Buchanan', N'Steven', N'Sales Manager', N'Mr.', CAST(0x00004EB600000000 AS DateTime), CAST(0x000085D100000000 AS DateTime), N'14 Garrett Hill', N'London', NULL, N'SW1 8JR', N'UK', N'(71) 555-4848')
INSERT [dbo].[Employees2] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (6, N'Suyama', N'Michael', N'Sales Representative', N'Mr.', CAST(0x00005A9800000000 AS DateTime), CAST(0x000085D100000000 AS DateTime), N'Coventry House
Miner Rd.', N'London', NULL, N'EC2 7JR', N'UK', N'(71) 555-7773')
INSERT [dbo].[Employees2] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (7, N'King', N'Robert', N'Sales Representative', N'Mr.', CAST(0x0000562F00000000 AS DateTime), CAST(0x0000861E00000000 AS DateTime), N'Edgeham Hollow
Winchester Way', N'London', NULL, N'RG1 9SP', N'UK', N'(71) 555-5598')
INSERT [dbo].[Employees2] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (8, N'Callahan', N'Laura', N'Inside Sales Coordinator', N'Ms.', CAST(0x000052C800000000 AS DateTime), CAST(0x0000865C00000000 AS DateTime), N'4726 - 11th Ave. N.E.', N'Seattle', N'WA', N'98105', N'USA', N'(206) 555-1189')
INSERT [dbo].[Employees2] ([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone]) VALUES (9, N'Dodsworth', N'Anne', N'Sales Representative', N'Ms.', CAST(0x00005E4400000000 AS DateTime), CAST(0x0000875B00000000 AS DateTime), N'7 Houndstooth Rd.', N'London', NULL, N'WG2 7LT', N'UK', N'(71) 555-4444')
SET IDENTITY_INSERT [dbo].[Employees2] OFF

Open in new window




I have this query but I don't think its correct.

DELETE FROM [Northwind].[dbo].[EmployeesTest2]
WHERE EXISTS (
              SELECT * FROM [Northwind].[dbo].[EmployeesTest1]
              WHERE [LastName] = 'King'
             )


In the subquery I'm selecting all records from EmployeesTest1 with LastName = 'King'

What I want my query to do is ....
use the subquery result to then delete all records in EmployeesTes2 with  LastName = 'King' which I got from the subquery inside the WHERE EXISTS clause

How would I revise my syntax to do this?
0
Comment
Question by:maqskywalker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 41897685
It's missing a JOIN between the two tables. Try something like this

DELETE FROM [Northwind].[dbo].[EmployeesTest2] main
WHERE EXISTS (
              SELECT 1
              FROM   [Northwind].[dbo].[EmployeesTest1] dupe
              WHERE dupe.[LastName] = 'King'
               AND     dupe.EmployeeID = main.EmployeeID
             )

Open in new window


BTW, you can always test it with a SELECT first to verify the results are correct

SELECT main.* 
FROM  [Northwind].[dbo].[EmployeesTest2] main
WHERE EXISTS (
              SELECT 1
              FROM   [Northwind].[dbo].[EmployeesTest1] dupe
              WHERE dupe.[LastName] = 'King'
               AND     dupe.EmployeeID = main.EmployeeID
             )

Open in new window

0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41897701
Try..

DELETE FROM [Northwind].[dbo].[EmployeesTest2] main
INNER JOIN [Northwind].[dbo].[EmployeesTest1] dupe
ON dupe.EmployeeID = main.EmployeeID
WHERE dupe.[LastName] = 'King'

Open in new window


SELECT * FROM [Northwind].[dbo].[EmployeesTest2] main
INNER JOIN [Northwind].[dbo].[EmployeesTest1] dupe
ON dupe.EmployeeID = main.EmployeeID
WHERE dupe.[LastName] = 'King'

Open in new window

0
 
LVL 1

Author Comment

by:maqskywalker
ID: 41897733
Hi _agx_ and Pawan,

I tried both of your Select statements and they where.

But when I tried both of your DELETE statements i get the same error on both.
I get this message:

Incorrect syntax near 'main'.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 52

Accepted Solution

by:
_agx_ earned 250 total points
ID: 41897777
Oops. For SQL Server the DELETE alias needs to be separate.  Use this:

DELETE FROM main
FROM   [Northwind].[dbo].[EmployeesTest2] main
WHERE EXISTS (
              SELECT 1
              FROM   [Northwind].[dbo].[EmployeesTest1] dupe
              WHERE dupe.[LastName] = 'King'
               AND     dupe.EmployeeID = main.EmployeeID
             )

Open in new window

0
 
LVL 1

Author Comment

by:maqskywalker
ID: 41897888
thanks
0
 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 total points
ID: 41898251
Sorry for the incorrect syntax...  :)

DELETE main
 FROM [Northwind].[dbo].[EmployeesTest2] main
 INNER JOIN [Northwind].[dbo].[EmployeesTest1] dupe ON dupe.EmployeeID = main.EmployeeID
 WHERE dupe.LastName = 'King'

Open in new window


Here is the complete script.

 
 CREATE TABLE [dbo].[EmployeesTest2]
 (
	EmployeeID INT
 )
 GO

 INSERT INTO [dbo].[EmployeesTest2]
 VALUES (1),(2)
 GO

 CREATE TABLE [dbo].[EmployeesTest1]
 (
	EmployeeID INT
	,LastName VARCHAR(100)
 )
 GO

 INSERT INTO [dbo].[EmployeesTest1]
 VALUES (1,'King'),(2,'Queen')
 GO

 DELETE main
 FROM [dbo].[EmployeesTest2] main
 INNER JOIN [dbo].[EmployeesTest1] dupe ON dupe.EmployeeID = main.EmployeeID
 WHERE dupe.LastName = 'King'

 

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 41898334
Yes, we figured it out already ;-)
0
 
LVL 1

Author Closing Comment

by:maqskywalker
ID: 41898731
thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question