Post reply

In Case statement i need to pass subquery. what's the bestway to do it.

  • August 23, 2018 at 6:34 pm

    #362207

    In Case statement i need to pass subquery. what's the bestway to do it. Can i declare it before do update? Please advise.

    UPDATE tab1     
        set fld2 = Case when t2.total < t2.col1 then t2.col7
                when t2.total >=t2.col2 and t2.total1 <= t2.col3 then t2.col8
                             when t2.total>= t2.col3 then t2.col9 end
        from tab1 t1
    inner join tab2 t2 on t1.id=t2.pid

    i need to change like this:
    UPDATE tab1     
        set fld2 = Case when t2.total < (select t2.col4 from tab2 where col10=1)then (select t2.col7 from tab2 where col10=1)
                when t2.total >=t2.col2 and t2.total1 <= t2.col3 then t2.col8
                             when t2.total>= t2.col3 then t2.col9 end 
        from tab1 t1
    inner join tab2 t2 on t1.id=t2.pid

  • August 24, 2018 at 12:37 am

    #2003149

    Hi,

    Can you please post the DDL needed to create the required tables (also with some sample data)?

  • August 24, 2018 at 1:44 am

    #2003156

    The SQL you have will work, however, I doubt it's what you're after. I've added a couple of extra aliases and a comment to explain this:

    UPDATE tab1 --Will create a cartesian product as tab1 and t1 are not the same, could cause odd results
    SET fld2 = CASE WHEN t2.total < (SELECT t2.col4 FROM tab2 sq1 WHERE sq1.col10 = 1) THEN (SELECT t2.col7 FROM tab2 sq2 WHERE sq2.col10 = 1)
          WHEN t2.total >= t2.col2 AND t2.total1 <= t2.col3 THEN t2.col8
          WHEN t2.total >= t2.col3 THEN t2.col9
        END
    FROM tab1 t1
      INNER JOIN tab2 t2 ON t1.id = t2.pid;

    Firstly, note the subqueries. Then them you are returning t2.col4. t2 is an alias of a table outside of your subquery, thus it would the value from t2.col (the table in your JOIN, not the subquery). Also, you will actually create a cartesian product with your UPDATE, as you do.n't reference the alias in  your FROM.

    I believe, what you are after is the below, which doesn't need a subquery:

    UPDATE t1
    SET fld2 = CASE WHEN t2.total < t2c10.col4 THEN t2c10.col7
           WHEN t2.total >= t2.col2 AND t2.total1 <= t2.col3 THEN t2.col8
          WHEN t2.total >= t2.col3 THEN t2.col9
       END
    FROM tab1 t1
      INNER JOIN tab2 t2 ON t1.id = t2.pid
      INNER JOIN tab2 t2c10 ON t2c10.col10 = 1;

    Also, just as a side note, it's called a CASE expression in T-SQL; there are no CASE statements. A CASE expression returns a scalar value (which the above does), where a CASE statement is a logical flow thing (for example CASE WHEN X = Y THEN UPDATE TABLE T... WHEN X = Z THEN DELETE FROM... etc). That kind of flow doesn't exist in SQL Server, a IF...ELSE would have to be used instead.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply