How do I update tables in SQL so that related strings match?

Question

Suppose I have 2 tables

Table1
ID Path
-- ---- 
1  PathA
2  PathB
3  PathC
4  PathD

Table2
ID Path  Table1ID
-- ----  --------
23 PathA 1
24 PathX 2
25 PathC 3
26 PathZ 4

In the above, PathX should be PathB and PathZ should be PathD

How do I sync all the path values in Table2 with values in Table 1, the tables could be large so would like to check for all values that do not match and update them. Cheers

Using MS SQL Server 2005

Answer 1

I suppose this should do the trick...

UPDATE Table2 SET
    Path = t1.Path
FROM Table2 t2
INNER JOIN Table1 t1 ON
    t1.ID = t2.Table2ID
WHERE
    t1.Path != t2.Path;

Edit: Sorry I believe I misinterpreted your request. I thought you wanted Table2 values into Table1. I've changed it to be the other way around.

Answer 2

If i am understanding your question correctly this will update the paths on table2 with values from table1

Update Table2
    set path = b.path
    From Table2 a
    Left outer join Table1 b
    on a.Table1ID = b.ID
    Where ltrim(rtrim(a.Path)) <> ltrim(rtrim(b.Path))

Answer 3

The following is I believe the simplest:

 UPDATE TableB SET Path = TableA.Path 
 FROM TableA
 WHERE TableB.Id = TableA.ID AND TableB.Path <> TableA.Path

Answer 4

Your example demonstrates how you can (will) get anomalies if you do not normalize your tables.

In Table2 either store Table1ID or Path but not both, you can query for the other (perhaps recreate your current Table2 as a VIEW. Whichever column you choose for Table2, ensure you have a foreign key on it referencing Table1.

Answer 5

The best way

UPDATE TableB SET Path = TableA.Path 
 FROM TableA
 WHERE TableB.Id = TableA.ID AND TableB.Path <> TableA.Path