Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.(Subquery) Repeat

Question

I have a problem with this query. I want to do the next:

The records that aren't in the same table, should be add it using the other table.

DECLARE @account INT = 8825

INSERT INTO [Fleet].[dbo].[AccountsReports] ([AccountId], [ReportId], [ReportName])
    SELECT
        @account AccountId,
        [ReportId],
        [Name] 
    FROM
        [Fleet].[dbo].[Reports] 
    WHERE
        ReportId IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,54) 
        AND ReportId NOT IN (SELECT @account AccountId, [ReportId], [Name] 
                             FROM [Fleet].[dbo].[Reports] 
                             WHERE ReportId IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,54)
                            )

This shows an exception:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

The queries separated are working fine. Can you help me to learn what my problem is?

Thanks

D-Shih · Accepted Answer · 2018-09-05T23:56:59.447

1

if I understand correctly, you need to use not exists instead of NOT IN, because of IN only can compare one column value in the subquery.

If you want to compare multiple columns you can use exists

look like this.

insert into [Fleet].[dbo].[AccountsReports]
                            ([AccountId],
                            [ReportId],
                            [ReportName])
SELECT
    @account AccountId 
    ,[ReportId]
    ,[Name] 
FROM [Fleet].[dbo].[Reports] t1
where ReportId in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,54) and
not exists (
        SELECT
            1
        FROM [Fleet].[dbo].[Reports] t2
        WHERE t1.ReportId = t2.ReportId  and t1.Name = t2.Name
)

edited Sep 05 '18 at 23:56

answered Sep 05 '18 at 23:43

D-Shih

44,943
6
31
51

D-shih, thanks for your comment. Let me explain you to give you more information. The table contains reports identified by Id, the id's that aren't should be add with insert, your query is good. – Ric_hc Sep 05 '18 at 23:55
@Ric_hc Ok I see I remove the condition. – D-Shih Sep 05 '18 at 23:57
Great D-Shih!!!!1 Exactly i needed.... Thanks my friend!!! – Ric_hc Sep 06 '18 at 00:01
Just a note, with the ANSI SQL feature "_Row and table constructors_" `IN` can be used for several columns, e.g. `WHERE (t1.ReportId, t1.Name) IN (select t2.ReportId, t2.Name from t2)`. Although not supported by all dbms products. When it comes to `NOT IN`, its null value behavior is a bit tricky, so I'd recommend `NOT EXISTS` instead - just as in your answer. – jarlh Sep 06 '18 at 06:29

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.(Subquery) Repeat

1 Answers1