The user of Exists in SQL

Question

I have searched an while their are multiple entries for my issue they do not assist me in pinpointing my syntax issue.

the error is

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

Here is my code

DECLARE @NewUserID INT;
DECLARE @OldUserID INT;
DECLARE @CID INT;
DECLARE @NewMar VARCHAR;
DECLARE @LocalUserID INT;
DECLARE @CallDate DATETIME;

SET @OldUserID = 1891
SET @NewUserID = 1868;
SET @CID = (SELECT TOP 1 *
            FROM   contact
            WHERE  EXISTS (SELECT TOP 1 mar
                           FROM   marselect
                           WHERE  userid = @OldUserID
                                  AND mar NOT LIKE '%branch%')
            ORDER  BY NEWID());

SET @LocalUserID = 1868;
SET @CallDate = '1900-01-01 00:00:00.000';


--move account to new owner--
UPDATE contact
SET    mar = (SELECT TOP 1 *
              FROM   marselect
              WHERE  userid = @NewUserID)
WHERE  contactid = @cid;

--add history to the account--
INSERT INTO ContactHistory
            (contactid,
             historydate,
             contactResultID,
             UserID,
             ActivityType,
             Note,
             StartTime,
             Endtime,
             CampaignID,
             QtyPhones)
VALUES     (@CID,
            GETDATE(),
            96,
            @LocalUserID,
            'Move',
            'Account Reassigned to ' + @NewMar + '',
            GETDATE(),
            GETDATE(),
            0,
            NULL);

--add callback to the account--
INSERT INTO callback
            (contactid,
             userid,
             contacthistoryid,
             contactLabelRequestID,
             callbackDate,
             LastcontacthistoryID,
             lastattempt,
             closed,
             closeddate,
             closeduserid,
             callbacktype,
             insertdate,
             callnote,
             hastime)
VALUES      (@CID,
             @NewUserID,
             0,
             NULL,
             @CallDate,
             0,
             NULL,
             0,
             NULL,
             NULL,
             'Call',
             GETDATE(),
             'Call created automatically after account reassigned',
             0)

I am not understanding why my query does not work. It works as a stand-alone query but when I use it in this sense it does not work. I have other errors of the same type in the code, but I figure that if I can understand and solve one I am confident that I can resolve the others.

the error is on this line-

set @CID = (select top 1 * from contact where exists (select top 1 mar from marselect where userid = @OldUserID and mar not like '%branch%') order by newid());

possible duplicate of [Only one expression can be specified in the select list when the subquery is not introduced with EXISTS](http://stackoverflow.com/questions/1904314/only-one-expression-can-be-specified-in-the-select-list-when-the-subquery-is-not) — Allan, Feb 24 '15 at 18:29
@Allan, the error message is the same, but the reasoning that caused the problem is different. :) — David, Feb 24 '15 at 18:31
@David, Admittedly, it's assignment versus comparison, but the source of the problem is the same: the sub-query is providing multiple columns where a single column is expected. — Allan, Feb 24 '15 at 18:33
thank you all for your input. Yes it was the same as many others but it was hard to understand the error message. "the sub-query is providing multiple columns where a single column is expected." was what I was needing to understand the error. — Mark Hart, Feb 24 '15 at 18:55

score 3 · Accepted Answer · answered Feb 24 '15 at 18:29

Remove * and keep the column name that you want to assign to @CID

Also there is no use in keeping top 1 inside exists

set @CID = 
(
select top 1 CID --here remove * and keep column name
from contact 
where exists (select mar -- Removed Top 1 here
              from marselect 
              where userid = @OldUserID and mar not like '%branch%') 
order by newid()
);

The user of Exists in SQL

1 Answers1