none
T SQL-Update query required for a table-T1

    Question

  • Table1

    Create table T1
    C1 varchar(50)


    Insert into T1 Values ('TCR-12345ABCDEF')
    GO
    Insert into  T1 Values('TCR-13245HIJKLM')
    GO
    Insert into  T1 Values('TCR-14567RSTUV')
    Table2

    Create Table T2

    (C1 varchar (50),
    C2  varchat (50),
    C3  varchar(50),
    C4  varchar(50)
    )

    insert into T2 values('12345','ABCDEF','98765','PQRST')
    GO
    insert into T2 values('13245','HIJKLM','45678','VWXYZ')


    expected output after we apply the update query to T1.I am here looking for the "update query" to T1.

    select * from T1

    TCR-98765PQRST
    TCR-45678VWXYZ
    TCR-14567RSTUV
    It means 5th character to 10th character of C1 of T1  should be replaced by C3 of T2,when 5th to 10th charecter of C1 of T1 matches with the C1 of T2.similary

    10th character to the last character of C1 of T1  should be replaced by C4 of T2,when 10th to last charecter of C1 of T1 matches with the C2 of T2.
    Hence the third row from T1 is unchaged as there is no matching record from T2.
    Sunday, September 08, 2013 12:31 PM

Answers

  • Try:

    UPDATE dbo.T1
    SET C1 = STUFF(T1.C1, 5, DATALENGTH(T1.C1) - 4, T2.C3 + T2.C4)
    FROM dbo.T2
    WHERE
    	T2.C1 + T2.C2 = SUBSTRING(T1.C1, 5, DATALENGTH(T1.C1) - 4);
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by RSingh() Sunday, September 08, 2013 2:33 PM
    • Marked as answer by RameshDravid Monday, September 09, 2013 1:17 AM
    Sunday, September 08, 2013 12:58 PM