locked
table update RRS feed

  • Question

  • Sorry I didn’t rephrase my original question correct so I’m trying agian

    I have a table with one field having an entry staring with “ on over 10,000 records.

    I can do a select with WHERE and get all LIKE ‘”%’  but what is the easiest way to replace the record from another table?

    The other table breaks out the name field fname lname but the table I want to update does not. This is the only common field for both tables.

    Tuesday, December 4, 2012 4:50 PM

Answers

  • update T1 set Id = T2.Id
    
    from Table1 T1 inner join Table2 T2
    ON T1.User = coalesce(T2.fname+' ','') + T2.lname
    
    where T1.ID LIKE '"%' -- starts with "
    2 tries already, re-trying 3rd time.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, December 4, 2012 6:02 PM

All replies

  • Hi, What mean "replace"? You want to update table with where? Update table1 set field="something new" from table1 where field2 like "%what we try to found%" You mean this?
    Tuesday, December 4, 2012 5:45 PM
  • i'm looking to update table1 ID field from with the value of table 2 ID where table1 ID LIKE ‘”%’  . 

    The two tables look like this:

    Table1 
    ID      user
    "abc   john smith

     Table2  
    fname  lname   ID
    John    Smith    abcd  -  So I need update the ID field in table1 with the ID from table2.

    Tuesday, December 4, 2012 5:55 PM
  • update T1 set Id = T2.Id
    
    from Table1 T1 inner join Table2 T2
    ON T1.User = coalesce(T2.fname+' ','') + T2.lname
    
    where T1.ID LIKE '"%' -- starts with "
    2 tries already, re-trying 3rd time.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, December 4, 2012 6:02 PM
  • DECLARE @tab1 TABLE(Id VARCHAR(20),UName VARCHAR(20))
    INSERT INTO @tab1
    SELECT '"abc','john smith' UNION ALL
    SELECT 'ddef','aaa bbb' UNION ALL
    SELECT '"ghi','ccc ddd'
    SELECT * FROM @tab1;
    DECLARE @tab2 TABLE(FName VARCHAR(20),LName VARCHAR(20),Id VARCHAR(20))
    INSERT INTO @tab2
    SELECT 'John','Smith','abcd' UNION ALL
    SELECT 'Aaa','Bbb', 'ddee' UNION ALL
    SELECT 'Ccc','Ddd','ghik'
    SELECT * FROM @tab2;
    UPDATE a
    SET a.Id=b.Id
    FROM @tab1 a
    INNER JOIN @tab2 b
    ON b.FName=SUBSTRING(UName,1,CHARINDEX(' ',UName))
    AND b.LName=SUBSTRING(UName,CHARINDEX(' ',UName)+1,LEN(UName))
    WHERE a.Id LIKE '"%'
    SELECT * FROM @tab1;


    Narsimha

    Tuesday, December 4, 2012 6:15 PM
  • You make it look so easy! thank you, that is perfect.

    Tuesday, December 4, 2012 6:21 PM
  • can you recommend any t-sql training
    Tuesday, December 4, 2012 8:51 PM
  • Well, there can be different ways to train yourself. You can read SQL Server related blogs, articles, books. You can watch PluralSight SQL Server related videos.

    There is ton information available in the Internet, you just need to choose. Say, I know that Kalman Toth has training available on his website

    http://sqlusa.com/

    You can also master some T-SQL using ww3schools.com

    http://www.w3schools.com/sql/default.asp


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, December 4, 2012 8:59 PM