Using count variable in if condtion in sql server

Question

Hi all,

I am trying to do update/insert into table based on a particular column count. If count(COL1) is > 1 or <1 or =1 then based on that it should be copied to other table . so have written below code where all datatypes are VARCHAR except date column.

DECLARE @CNT INT
SELECT @CNT = CNT FROM
(
SELECT COUNT(T1.[Retails]) CNT
FROM [DBO].[TABLE1] T1
JOIN [DBO].TABLE2 T2
ON LOWER(T2.Products]) = LOWER(T1.Products)
WHERE T1.[Retails] <> ' ' AND (T1.Products) <> ' '
AND T1.[DATE1] >= DATEADD(MONTH, -3, GETDATE())
AND T1.[DATE1] < GETDATE()
GROUP BY T1.[Retails]
) DERIVEDTABLE

IF @CNT = 1 THEN
UPDATE [DBO].TABLE2
SET [Retails] = (SELECT T2.[Retails] FROM [DBO].[TABLE1] T1
JOIN [DBO].TABLE2 T2
ON LOWER(T2.Products]) = LOWER(T1.Products)
WHERE T1.[Retails] <> ' ' AND (T1.Products) <> ' '
AND T1.[DATE1] >= DATEADD(MONTH, -3, GETDATE())
AND T1.[DATE1] < GETDATE()

But though the @CNT = 1 in the if condition am getting few more rows extra rows , where @CNT is 2 , 3, 4 etc. failing the purpose and also ending up with error saying multiple column fetched.

Can I not use @CNT variable in the if condition like the above ?

Answer 1

Your query don't fail on the IF condition, it fails later in the UPDATE statement with the sub-query; that one returns more the one result set (your real error message is a different one then that you posted).
Without knowing table design, existing data and the business logic it's difficult to assist here.

Answer 2

Hi @Charan P ,

It could be better for you to provide the CREATE TABLE statements for your tables (TABLE1 and TABLE2) together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

What would you do if count(COL1) is > 1 or <1 or =1?

I created an example from my side. You could check whether it is helpful to you.

drop table if exists [TABLE1]  
drop table if exists [TABLE2]  
  
create table [TABLE1]  
([COL1] varchar(20),  
COL2 varchar(20),  
[DATE1] date  
)  
  
create table [TABLE2]  
(  
col1 varchar(20),  
[HDR1] varchar(20)  
)  
  
insert into [TABLE1] values   
('a','aa','2020-10-10'),  
('b','bb','2020-09-10'),  
('c','cc','2020-08-10')  
  
insert into [TABLE2]([HDR1])  values  
('aa'),  
('bb')  
  
select * from [TABLE1]  
  
select * from [TABLE2]  

Output:

COL1 COL2 DATE1  
a aa 2020-10-10  
b bb 2020-09-10  
c cc 2020-08-10  
  
col1 HDR1  
NULL aa  
NULL bb  

Then we could proceed with the update TABLE2 whatever the count is.

UPDATE T  
SET T.[COL1] = T2.[COL1]  
FROM [DBO].TABLE2 T  
JOIN [DBO].TABLE1 T2  
ON LOWER(T.[HDR1]) = LOWER(T2.COL2)  
WHERE T2.[COL1] <> ' ' AND (T2.COL2) <> ' '  
AND T2.[DATE1] >= DATEADD(MONTH, -3, GETDATE())  
AND T2.[DATE1] < GETDATE()  

select * from [TABLE2]  

Output:

col1 HDR1  
a aa  
b bb  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @Charan P ,

You could also refer below:

DECLARE @CNT INT  
  
SELECT @CNT = CNT FROM  
(  
SELECT COUNT(T2.[COL1]) CNT  
FROM [DBO].[TABLE1] T2  
JOIN [DBO].TABLE2 T  
ON LOWER(T.[HDR1]) = LOWER(T2.COL2)  
WHERE T2.[COL1] <> ' ' AND (T2.COL2) <> ' '  
AND T2.[DATE1] >= DATEADD(MONTH, -3, GETDATE())  
AND T2.[DATE1] < GETDATE()  
) DERIVEDTABLE  
  
IF @CNT >= 1   
BEGIN  
UPDATE T  
SET T.[COL1] = T2.[COL1]  
FROM [DBO].TABLE2 T  
JOIN [DBO].TABLE1 T2  
ON LOWER(T.[HDR1]) = LOWER(T2.COL2)  
WHERE T2.[COL1] <> ' ' AND (T2.COL2) <> ' '  
AND T2.[DATE1] >= DATEADD(MONTH, -3, GETDATE())  
AND T2.[DATE1] < GETDATE()  
END  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

The problem is, as Olaf already pointed out, caused by your subquery returning more than one row.
The IF does fail, cause you use the wrong COUNT() value.

The UPDATE requires that COUNT(*) = 1.
But you used COUNT(columName), which returns the number of non-null values in that column with duplicates. btw, this is equivalent to COUNT(ALL columnName).

DECLARE @Sample TABLE (  
    Payload INT  
);  
  
INSERT INTO @Sample ( Payload )  
VALUES ( NULL ) ,  
       ( 0 ) ,  
       ( 1 ) ,  
       ( 1 );  
  
SELECT COUNT(*) ,  
       COUNT(S.Payload) ,  
       COUNT(ALL S.Payload) ,  
       COUNT(DISTINCT S.Payload)  
FROM   @Sample S;  

See also COUNT (Transact-SQL)

I would test something like this:

WITH Counted  
AS ( SELECT T2.[COL1] AS Dst ,  
            T1.[COL1] AS Src ,  
            COUNT(*) OVER () AS Cnt  
     FROM   [dbo].TABLE2 T2  
            INNER JOIN [dbo].TABLE1 T1 ON LOWER(T2.[HDR1]) = LOWER(T1.COL2)  
     WHERE  T1.[COL1] <> ' '  
            AND T1.COL2 <> ' '  
            AND T1.[DATE1] >= DATEADD(MONTH, -3, GETDATE())  
            AND T1.[DATE1] < GETDATE())  
UPDATE C  
SET    C.Dst = C.Src  
FROM   Counted C  
WHERE  C.Cnt = 1;  

Depending on your use-case and context, I would consider using COLLATE instead of LOWER().

Answer 5

Hi @Charan P ,

Please provide CREATE TABLE statements for your tables together with INSERT statements with sample data instead of providing only one snapshot since all of helpers need to spend a lot of time to create and insert these tables.

I still have some concerns about your requirement. Please help check.

1. In your query, date1 should be the in the past 3 months. For example, 'Amazon.com' whose date is '2020-07-01' but appears in your desired output.
2. How to calculate the count and judge the count as 1? Per my understanding, in table1, in the past three months, all the retails except Costco have the count as 1.

I tried to comment the condition of date1 should be in the past 3 months in the query and made some modifications on your query.

I could get the output the same as in your snapshot. You could check whether it is helpful to you.

drop table if exists [TABLE1]  
drop table if exists [TABLE2]  
  
create table [TABLE1]  
(Retails varchar(100),  
Products varchar(100),  
[DATE1] date  
)  
  
create table [TABLE2]  
(  
Products varchar(100),  
Retails varchar(100)  
)  
  
insert into [TABLE1] values   
('Walmart','Vegetables','2019-11-10'),  
('Amazon.com','Bakery fresh','2020-07-01'),  
('The Kroger Co.','Snaks & candy','2020-8-1'),  
('Costco','Meat &Seafood','2020-08-08'),  
('Walmart','Clothes','2020-09-25'),  
('Costco','Vegetables','2020-09-25'),  
('Costco','Bakery fresh','2020-08-08'),  
('Costco','Snaks & candy','2020-09-25'),  
('Costco','Meat &Seafood','2020-08-08'),  
('Costco','Clothes','2020-08-08'),  
('Walgreens Boots Alliance','Clothes','2020-07-18'),  
('Dollar General','Clothes','2020-06-18'),  
('Dollar General','Bakery fresh','2020-07-20')  
  
insert into [TABLE2](Products)  values  
('Vegetables'),  
('Bakery fresh'),  
('Snaks & candy'),  
('Meat &Seafood'),  
('Clothes'),  
('Vegetables'),  
('Bakery fresh'),  
('Snaks & candy'),  
('Meat &Seafood'),  
('Clothes'),  
('Clothes'),  
('Clothes'),  
('Bakery fresh')  
  
;with cte as (  
SELECT T1.[Retails],COUNT(T1.[Retails]) CNT  
FROM [DBO].[TABLE1] T1  
WHERE T1.[Retails] <> ' ' AND (T1.Products) <> ' '  
--AND T1.[DATE1] >= DATEADD(MONTH, -3, GETDATE())  
--AND T1.[DATE1] < GETDATE()  
GROUP BY T1.[Retails]  
)   
, cte2 as   
(  
select Retails,Products   
from [DBO].[TABLE1]   
where Retails in (select Retails from cte where cnt=1)  
and [Retails] <> ' ' AND (Products) <> ' '  
--AND [DATE1] >= DATEADD(MONTH, -3, GETDATE())  
--AND [DATE1] < GETDATE()  
)  
,cte3 as (  
select Products,count(Products) count from cte2  
group by Products)  
  
UPDATE T2  
SET T2.[Retails] = T1.Retails  
FROM [DBO].TABLE2 T2  
JOIN cte2 T1  
ON LOWER(T2.Products) = LOWER(T1.Products)  
inner join cte3 t3  
on t3.Products=t2.Products  
where t3.count=1  
  
select * from [TABLE2]  

Output:

Products	Retails  
Vegetables	NULL  
Bakery fresh	Amazon.com  
Snaks & candy	The Kroger Co.  
Meat &Seafood	NULL  
Clothes	Walgreens Boots Alliance  
Vegetables	NULL  
Bakery fresh	Amazon.com  
Snaks & candy	The Kroger Co.  
Meat &Seafood	NULL  
Clothes	Walgreens Boots Alliance  
Clothes	Walgreens Boots Alliance  
Clothes	Walgreens Boots Alliance  
Bakery fresh	Amazon.com  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.