Problem with delete syntax in SQL – Error Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

I have this SQL query:

SELECT NumeroReloj, Badgenumber, Name, lastname, DEFAULTDEPTID
FROM [PBS].[dbo].[CAT_Empleados]
RIGHT JOIN [AccessControl].[dbo].[USERINFO]
ON [PBS].[dbo].[CAT_Empleados].[NumeroReloj] = [AccessControl].[dbo].[USERINFO].[Badgenumber] COLLATE Chinese_PRC_CI_AS
WHERE [AccessControl].[dbo].[USERINFO].[Badgenumber] COLLATE Chinese_PRC_CI_AS NOT IN (SELECT [NumeroReloj] FROM [PBS].[dbo].[CAT_Empleados])

There are 2 data bases, and 2 tables with different names.

With that query I get some users who are in table [AccessControl].[dbo].[USERINFO] but they are not in table [PBS].[dbo].[CAT_Empleados].

The Employee# column for [AccessControl].[dbo].[USERINFO] is named ‘Badgenumber’, and the Employee# column for [PBS].[dbo].[CAT_Empleados] is named ‘NumeroReloj’ (thats how match both results).

Well, that query is working fine, and now I want to delete in [AccessControl].[dbo].[USERINFO] the same results that I get in that Select query. I have tried by:

DELETE 
FROM [AccessControl].[dbo].[USERINFO] 
WHERE [AccessControl].[dbo].[USERINFO].[Badgenumber] IN (SELECT * FROM [PBS].[dbo].[CAT_Empleados] RIGHT JOIN [AccessControl].[dbo].[USERINFO] ON [PBS].[dbo].[CAT_Empleados].[NumeroReloj] = [AccessControl].[dbo].[USERINFO].[Badgenumber] COLLATE Chinese_PRC_CI_AS WHERE [AccessControl].[dbo].[USERINFO].[Badgenumber] COLLATE Chinese_PRC_CI_AS NOT IN (SELECT [NumeroReloj] FROM [PBS].[dbo].[CAT_Empleados])

But I’m getting this error message:

Msg 116, Level 16, State 1, Line 3 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Answer

The problem you have is that your delete statement, you are asking to delete only employees with a badgenumber in a sub query which returns multiple fields, as denoted by “*”. So SQL doesn’t know what you want badgenumber to compare to.

Change the “*” in your subquery to match 1 field, that is the field you want to compare with the badgenumber.

Advertisement

Answer