SQL Server Performance

help on the select statement

Discussion in 'SQL Server 2005 General Developer Questions' started by dba_boston, Oct 21, 2010.

  1. dba_boston Member

    three table
    t1 one col with 3 row
    1
    2
    3t2 two col one row
    25t3 one col with 4 row
    1
    2
    3
    4I would like to insert the value from t1 into t3, but need to checking the duplicate value for col1 with t2 if it matches, then will use t2.col2 vaule,
    if not match, will add 100 as the new value
    so value for insert will be
    101
    5
    103Here is the script:insert
    into t1 values( 3)--select * from t1create
    table t2 ( c1 int, c2 int )insert
    into t2 values( 2, 5)--select * from t2create
    table t3 ( c1 int)insert
    into t3 values( 1)insert
    into t3 values( 2)insert
    into t3 values( 3)insert
    into t3 values( 4)--select * from t3
    Here is my code, but it returns error as The multi-part identifier "t2.c2" could not be bound.select
    c1=case when exists ( select t2.c1 from t2
    where t2.c1=t1.c1 ) then t2.c2else
    c1+100end
    from
    t1
    Thanks for help
  2. FrankKalis Moderator

    I might be missing something, but does this work for you?
    SELECT
    CASE
    WHEN T3.c1 IS NOT NULL AND T2.c1 IS NOT NULL
    THEN T2.c2
    ELSE T1.c1 + 100
    END
    FROM
    t1 T1
    LEFT JOIN
    t2 T2 ON T1.c1 = T2.c1
    LEFT JOIN
    t3 T3 ON T1.c1 = T3.c1;

Share This Page