Solved

using t-sql EXISTS

Posted on 2016-11-22
8
46 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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

776 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