Solved

using t-sql EXISTS

Posted on 2016-11-22
8
39 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 24

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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 1

Author Comment

by:maqskywalker
ID: 41897888
thanks
0
 
LVL 24

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

910 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

23 Experts available now in Live!

Get 1:1 Help Now