1. SQL  - Using Case Statement To Update A Table: 


    CREATE TABLE #t1 (ID INT, name VARCHAR(10), val1 INT, val2 INT)

    INSERT #t1  (ID , name , val1 , val2 )
    SELECT 1, 'aaa01', 10, 100
    UNION ALL
    SELECT 2, 'bbb01', 20, 200
    UNION ALL
    SELECT 3, 'aaa02', 30, 300
    UNION ALL
    SELECT 4, 'bbb02', 40, 400

    SELECT *
    FROM   #t1

    1 aaa01 10 100
    2 bbb01 20 200
    3 aaa02 30 300
    4 bbb02 40 400


    --Update rows 1 and 3
    UPDATE #t1 
    SET val2 = CASE 
    WHEN ID = 1 THEN 1000
    WHEN ID =3 THEN 3000
    ELSE Val2
    END 

    SELECT *
    FROM   #t1

    ID name val1 val2
    1 aaa01 10 1000
    2 bbb01 20 200
    3 aaa02 30 3000
    4 bbb02 40 400

    DROP TABLE #t1 

    0

    Add a comment

  2. Here is an example of what different join produce in SQL.


    Create and populate two tables:
    CREATE TABLE #t1 (ID INT, name VARCHAR(10), val1 INT, val2 INT)
    CREATE TABLE #t2 (ID INT, name VARCHAR(10), val1 INT, val2 INT)

    INSERT #t1  (ID , name , val1 , val2 )
    SELECT 1, 'aaa01', 10, 100
    UNION ALL
    SELECT 2, 'bbb01', 20, 200
    UNION ALL
    SELECT 3, 'aaa02', 30, 300
    UNION ALL
    SELECT 4, 'bbb02', 40, 400


    INSERT #t2  (ID , name , val1 , val2 )
    SELECT 3, 'ccc03', 10, 100
    UNION ALL
    SELECT 4, 'ddd03', 20, 200
    UNION ALL
    SELECT 5, 'ccc04', 30, 300
    UNION ALL
    SELECT 6, 'ddd04', 40, 400

    _________________________________________________________________________

    LEFT JOIN: 

    SELECT #t1.*, #t2.*
    FROM #t1
    LEFT JOIN #t2 ON #t1.id = #t2.id

    ID name val1 val2 ID name val1 val2
    1 aaa01 10 100 NULL NULL NULL NULL
    2 bbb01 20 200 NULL NULL NULL NULL
    3 aaa02 30 300 3 ccc03 10 100
    4 bbb02 40 400 4 ddd03 20 200

    _________________________________________________________________________

    RIGHT JOIN:

    SELECT #t1.*, #t2.*
    FROM #t1 
    RIGHT JOIN #t2 ON #t1.id = #t2.id 

    ID name val1 val2 ID name val1 val2
    3 aaa02 30 300 3 ccc03 10 100
    4 bbb02 40 400 4 ddd03 20 200
    NULL NULL NULL NULL 5 ccc04 30 300
    NULL NULL NULL NULL 6 ddd04 40 400



    _________________________________________________________________________

    INNER JOIN:


    SELECT #t1.*, #t2.*
    FROM #t1 
    INNER JOIN #t2 ON #t1.id = #t2.id 

    ID name val1 val2 ID name val1 val2
    3 aaa02 30 300 3 ccc03 10 100
    4 bbb02 40 400 4 ddd03 20 200

    _________________________________________________________________________

    JOIN:

    SELECT #t1.*, #t2.*
    FROM #t1 
    JOIN #t2 ON #t1.id = #t2.id 

    ID name val1 val2 ID name val1 val2
    3 aaa02 30 300 3 ccc03 10 100
    4 bbb02 40 400 4 ddd03 20 200
    _________________________________________________________________________

    FULL OUTER JOIN:

    SELECT #t1.*, #t2.*
    FROM #t1 
    FULL OUTER JOIN #t2 ON #t1.id = #t2.id 

    ID name val1 val2 ID name val1 val2
    1 aaa01 10 100 NULL NULL NULL NULL
    2 bbb01 20 200 NULL NULL NULL NULL
    3 aaa02 30 300 3 ccc03 10 100
    4 bbb02 40 400 4 ddd03 20 200
    NULL NULL NULL NULL 5 ccc04 30 300
    NULL NULL NULL NULL 6 ddd04 40 400

    0

    Add a comment

Blog Archive
Topics
Topics
Loading
Dynamic Views theme. Powered by Blogger. Report Abuse.