IF Condition Returning too Many Values

Question

I am pretty new to the t-sql world and am trying to create a query that will change a value based on multiple criteria.

TSH1 is the main table that values will be changed in. Freightview is the table that has the shipping amount I need to add into TSH1.

I want the query to look for matches between the tables and when there is one make a change to the FREIGHT line if it exists. If the FREIGHT line doesn't exist then it needs to add a line with the invoice amount from Freightview table.

My issue is the IF statement. It is returning two many values for the query to work. What do I need to change?

The last two queries are to return values that are not in each table.

SELECT *
FROM TSH1 T 
JOIN Freightview FR on FR.[Shippers number] = T.sonum
IF
    ((SELECT [Shippers number] FROM Freightview) = (SELECT sonum FROM TSH1 T WHERE EXISTS(SELECT * FROM TSH1 T WHERE T.productnum = 'FRT-OUT' OR T.productnum = 'FRT-IN' OR T.productnum = 'FRT')))
    
     
    BEGIN
    UPDATE TSH1 SET tcost = FR.[Invoice Amount] FROM TSH1 T INNER JOIN Freightview FR on FR.[Shippers number] = T.sonum 
    WHERE T.productnum = 'FRT-OUT' OR T.productnum = 'FRT-IN' OR T.productnum = 'FRT';
    END

    ELSE IF
    ((SELECT [Shippers number] FROM Freightview) = (SELECT sonum FROM TSH1 T WHERE NOT EXISTS(SELECT * FROM TSH1 T WHERE T.productnum = 'FRT-OUT' OR T.productnum = 'FRT-IN' OR T.productnum = 'FRT')))
    
    
    BEGIN
    SELECT *  INTO temp_table FROM TSH1 T INNER JOIN Freightview FR on FR.[Shippers number] = T.sonum
    WHERE FR.[Shippers number] = T.sonum AND NOT EXISTS (SELECT productnum from TSH1 T where T.productnum = 'FRT-OUT' OR T.productnum = 'FRT-IN' OR T.productnum = 'FRT');
    UPDATE temp_table SET temp_table.productnum = 'FRT', [Invoice Amount] = TT.tcost, temp_table.productid = '7240', temp_table.pd = 'FREIGHT', temp_table.qtyfulfilled = 1, 
    temp_table.tprice = 0, temp_table.stdcost = 0, temp_table.flag = 'D', temp_table.avgcost = NULL
    FROM temp_table TT 
    INNER JOIN Freightview FR on TT.sonum = FR.[Shippers number];
    UPDATE temp_table SET ID=NULL;
    DELETE x FROM (
    SELECT *, rn=row_number() over (partition by TT.sonum order by TT.soid)
    FROM temp_table TT
        ) x
    WHERE rn > 1;
    INSERT INTO TSH1 SELECT * FROM temp_table;
    DROP TABLE temp_table;
    END
    ELSE 
    BEGIN
    SELECT * 
    FROM TSH1 T 
    LEFT JOIN Freightview FR on T.sonum = FR.[Shippers number]
    WHERE FR.[Shippers number] IS NULL;
    END

    BEGIN
    SELECT * 
    FROM Freightview FR 
    LEFT JOIN TSH1_Backup T on T.sonum = FR.[Shippers number]
    WHERE T.sonum IS NULL;
    END

END```

Answer 1

With SQL, you typically have to "think in sets". For example, a select statement returns a set of values, not just a single value¹.

If I select * from T, the result might have multiple rows.

If I insert T1 select * from T2, multiple rows might be inserted into T1.

So, a statement like

if ((select c from T1) = (select c from T2))

Is sort of an odd construct. What exactly are we comparing here? On the left hand side we have zero or more rows from T1, and on the right hand side we zero or more rows from T2.

Now, you might be thinking to yourself...

Well the answer is obvious. If the two result sets are identical, then the equality comparison should return true, right?

Well... yes. It would be nice if we could do that. But that would require that SQL think of the result of a select statement as "an anonymous collection type with member-wise value equality semantics". And SQL is not that sophisticated as a language. In SQL, if you're comparing one thing to another with =, the left hand side and the right hand side should both be scalar types. "Single values", like an int, or a float, or a boolean. Not sets.

Fundamentally, it's the same reason why you can't do this:

create table T1(i int);
create table T2(j int);

if (T1 = T2) print 'tables had exactly the same content`;

So, how do you get the semantics "tell me if the contents of T1 and T2 exactly match?". There's no compact syntax to do this, you have to be verbose about it, there are lots of different ways you can "phrase" the question, and it's easy to make a mistake. Here's one correct way:

create table T1(i int);
create table T2(j int);

if not exists 
(
   select      *
   from        T1
   full join   T2 on T1.i = T2.j
   where       T1.i is null or T2.j is null
) print 'tables had exactly the same content';

The logic is "match every row that you can, and tell me if there are any rows that couldn't be matched".

Now, interestingly enough SQL doesn't "validate" the comparison until it actually gets its results, so if your select statements each happen to return just a single row and single column, then the result of the select statement is treated as a scalar value, not a set, and then the equality comparison works. I sort of wish it didn't, because it's inconsistent and confuses people:

create table T1(i int);
create table T2(j int);

insert T1 values (1);
insert T2 values (1);

-- This will unfortunately succeed, and do what you intuitively "expect".
if ((select i from T1) = (select j from T2)) 
   print 'tables both exactly one row with the same value';

But what if I put more rows into one of the tables?

create table T1(i int);
create table T2(j int);

insert T1 values (1), (2);
insert T2 values (1);

-- This will fail
if ((select i from T1) = (select j from T2)) 
   print 'tables both exactly one row with the same value';

The error is:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

You have some SQL that makes this same mistake:

if ((select  [shippers number] from  Freightview) = -- ...

I hope this answers your specific question about why you're getting the error. But hang on, let's go back and look at your requirements:

I want the query to look for matches between the tables and when there is one make a change to the FREIGHT line if it exists. If the FREIGHT line doesn't exist then it needs to add a line with the invoice amount from Freightview table.

So, you want a combination of insert and update, depending on the data. An "upsert".

TSQL has a statement which can do exactly this: Merge. Here's a simplified example to demonstrate how to use it.

create table T1(i int, c char);
create table T2(j int, c char);

insert T1 values (1, 'a');
insert T2 values (1, 'b'), (2, 'c');

merge    T1                   -- T1 will be "target" in the rest of the merge statement
using    T2 on t2.j = T1.i    -- T2 will be "source" in the rest of the merge statment

when matched then     
   update
   set T1.c = T2.c

-- "target" isn't an alias defined by me. It's defined by the structure of "merge"
-- So this condition translates to "if there is a row in T2 with no matching row in T1"
when not matched by target then 
   insert (i, c)
   values (T2.j, T2.c);

select * from T1;

/* result:
i  c
----
1  b
2  c
*/

Formatting merge statements is hard, I've never found a way to do it that I am totally happy with.

---

^{1 That's not really accurate. SQL allows duplicate rows to exist in tables, result sets, and so on. In mathematics sets cannot have duplicate members. So technically you have to "think in bags". But people tend to say "think in sets" despite this.}