Solved

using t-sql EXISTS

Posted on 2016-11-22
8
23 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 18

Expert Comment

by:Pawan Kumar Khowal
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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:maqskywalker
ID: 41897888
thanks
0
 
LVL 18

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now