none
update sql based on 3 columns values not in 3 column value from another table RRS feed

  • Question

  • Hi There 

    I have table A as shown below 

    Col1  Col2 Col3 col4 col5

    1       2      3     a     L

    4       5      6     b     L

    Table B as 

    Col1 Col2 Col3 

    4       5     6

    The combination of col1, col2, Col3 are unique columns. I want update col5  under table A if don't find similar COl1 Col2 Col3 in table B.

    My output under Table a should be as , The first row doesn't show up in error table (Table B) so i need to mark col5 under table A as 'C'

    Col1  Col2 Col3 col4 col5

    1       2      3     a     C

    4       5      6     b     L

    Thanks

    Raj


    • Edited by Rajm0019 Tuesday, December 3, 2019 8:26 PM
    Tuesday, December 3, 2019 8:12 PM

Answers

  • DECLARE @Table TABLE (Col1 INT,Col2 INT,Col3 INT, Col4 CHAR(1), Col5 CHAR(1))
    INSERT INTO @Table
    SELECT 1,2,3,'a','L'
    INSERT INTO @Table
    SELECT 4,5,6,'a','L'
    
    DECLARE @Table2 TABLE (Col1 INT,Col2 INT,Col3 INT)
    
    INSERT INTO @Table2
    SELECT 4,5,6
    
    UPDATE A SET Col5='C'
    FROM @table A WHERE NOT EXISTS ( SELECT 1 FROM @Table2 B WHERE A.Col1=B.Col1 AND A.Col2=B.Col2 AND A.Col3=B.Col3)
    
    SELECT * FROM @Table


    Hope it Helps!!

    • Marked as answer by Rajm0019 Tuesday, December 3, 2019 9:12 PM
    Tuesday, December 3, 2019 8:43 PM

All replies

  • DECLARE @Table TABLE (Col1 INT,Col2 INT,Col3 INT, Col4 CHAR(1), Col5 CHAR(1))
    INSERT INTO @Table
    SELECT 1,2,3,'a','L'
    INSERT INTO @Table
    SELECT 4,5,6,'a','L'
    
    DECLARE @Table2 TABLE (Col1 INT,Col2 INT,Col3 INT)
    
    INSERT INTO @Table2
    SELECT 4,5,6
    
    UPDATE A SET Col5='C'
    FROM @table A WHERE NOT EXISTS ( SELECT 1 FROM @Table2 B WHERE A.Col1=B.Col1 AND A.Col2=B.Col2 AND A.Col3=B.Col3)
    
    SELECT * FROM @Table


    Hope it Helps!!

    • Marked as answer by Rajm0019 Tuesday, December 3, 2019 9:12 PM
    Tuesday, December 3, 2019 8:43 PM
  • ;with mycte as (
    Select Col1,Col2,Col3 from Table1
    Except
    Select Col1,Col2,Col3 from Table2
    )
    Merge Table1 as tgt
    Using mycte as src ON tgt.Col1=src.Col1 
    AND tgt.Col2=src.Col2 
    AND tgt.Col3=src.Col3
    When matched then 
    UPDATE
    SET Col5='C';

    Tuesday, December 3, 2019 9:13 PM
    Moderator