locked
Update Query RRS feed

  • Question

  • User-1068576637 posted
    Table1  
    Column1 Column2
    100A 1/9/2019
    560B 10/1/2019
    110AB  
    890CD  

    Table2  
    ColumnA ColumnB
    100A 110AB
    560B 890CD

    Using Sql server2008 , i Want to update Column2 in Table1, Based on Table 2 .

    Whatever date is applicable for columnA value should take same datevalue for ColumnB also.

    Example Result should be

    Result of Table 1  
    Column1 Column2
    100A 1/9/2019
    560B 10/1/2019
    110AB 1/9/2019
    890CD 10/1/2019

    Kindly give the update query 

    Wednesday, January 9, 2019 9:25 AM

Answers

  • User-2082239438 posted

    Check this.

    CREATE TABLE TABLE1
    (
    	Column1 VARCHAR(20),Column2 VARCHAR(20)
    )
    
    INSERT INTO TABLE1
    (
    	Column1,Column2
    )
    VALUES('100A','1/9/2019')
    INSERT INTO TABLE1
    (
    	Column1,Column2
    )
    VALUES('560B','10/1/2019')
    INSERT INTO TABLE1
    (
    	Column1,Column2
    )
    VALUES('110AB','')	 
    INSERT INTO TABLE1
    (
    	Column1,Column2
    )
    VALUES('890CD','')
    
    
    CREATE TABLE Table2
    (
    	Column1 VARCHAR(20),Column2 VARCHAR(20)
    )
    INSERT INTO Table2
    (
    	Column1,Column2
    )
    VALUES('100A','110AB')	 
    INSERT INTO Table2
    (
    	Column1,Column2
    )
    VALUES('560B','890CD')
    
    
    --SELECT TABLE1.Column1,ISNULL((SELECT TOP 1 TABLE1.Column2 FROM TABLE1 WHERE TABLE1.Column1=A.Column1),TABLE1.Column2) AS Column2
    --FROM TABLE1
    --LEFT JOIN Table2 AS A ON TABLE1.Column1=A.Column2
    
    UPDATE TABLE1 SET Column2=ISNULL((SELECT TOP 1 TABLE1.Column2 FROM TABLE1 WHERE TABLE1.Column1=A.Column1),TABLE1.Column2)
    FROM TABLE1
    LEFT JOIN Table2 AS A ON TABLE1.Column1=A.Column2
    
    SELECT *
    FROM TABLE1
    
    
    -------------------OUTPUT
    --Column1	Column2
    --100A	1/9/2019
    --560B	10/1/2019
    --110AB	1/9/2019
    --890CD	10/1/2019
    
    DROP TABLE TABLE1
    DROP TABLE TABLE2
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 9, 2019 10:21 AM

All replies

  • User-2082239438 posted

    Use below query.

    UPDATE Table1 SET Table1.Column2=Table2.ColumnA
    FROM Table1
    INNER JOIN Table2 ON Table1.ColumnA=Table2.ColumnA

    Wednesday, January 9, 2019 9:42 AM
  • User-1068576637 posted

    You have mistaken , i want to update date column in table 1 of respective row.

    Wednesday, January 9, 2019 9:49 AM
  • User-2082239438 posted

    Check this.

    CREATE TABLE TABLE1
    (
    	Column1 VARCHAR(20),Column2 VARCHAR(20)
    )
    
    INSERT INTO TABLE1
    (
    	Column1,Column2
    )
    VALUES('100A','1/9/2019')
    INSERT INTO TABLE1
    (
    	Column1,Column2
    )
    VALUES('560B','10/1/2019')
    INSERT INTO TABLE1
    (
    	Column1,Column2
    )
    VALUES('110AB','')	 
    INSERT INTO TABLE1
    (
    	Column1,Column2
    )
    VALUES('890CD','')
    
    
    CREATE TABLE Table2
    (
    	Column1 VARCHAR(20),Column2 VARCHAR(20)
    )
    INSERT INTO Table2
    (
    	Column1,Column2
    )
    VALUES('100A','110AB')	 
    INSERT INTO Table2
    (
    	Column1,Column2
    )
    VALUES('560B','890CD')
    
    
    --SELECT TABLE1.Column1,ISNULL((SELECT TOP 1 TABLE1.Column2 FROM TABLE1 WHERE TABLE1.Column1=A.Column1),TABLE1.Column2) AS Column2
    --FROM TABLE1
    --LEFT JOIN Table2 AS A ON TABLE1.Column1=A.Column2
    
    UPDATE TABLE1 SET Column2=ISNULL((SELECT TOP 1 TABLE1.Column2 FROM TABLE1 WHERE TABLE1.Column1=A.Column1),TABLE1.Column2)
    FROM TABLE1
    LEFT JOIN Table2 AS A ON TABLE1.Column1=A.Column2
    
    SELECT *
    FROM TABLE1
    
    
    -------------------OUTPUT
    --Column1	Column2
    --100A	1/9/2019
    --560B	10/1/2019
    --110AB	1/9/2019
    --890CD	10/1/2019
    
    DROP TABLE TABLE1
    DROP TABLE TABLE2
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 9, 2019 10:21 AM
  • User-1068576637 posted

    Thanks  you very much

    Wednesday, January 9, 2019 10:32 AM
  • User77042963 posted
    Merge TABLE1 tgt
    using (
     Select t1.Column1,t.dt as Column2 from TABLE1 t1 join(
    Select t2.Column1 ,t2.Column2,t1.Column2  dt from  TABLE1 t1   join    TABLE2 t2 on t1.Column1=t2.Column1
     ) t on  t1.Column1=t.Column1 or  t1.Column1=t.Column2) src on tgt.Column1=src.Column1
     WHEN MATCHED THEN
     UPDATE SET Column2=src.Column2;
    
     Select * from TABLE1

    Wednesday, January 9, 2019 2:33 PM