newbie question: update across 2 tables
-
I have 2 tables. I want to use info from the second table to update column values in the first. Consider: T1: id time1 time2 1 8:00 null 2 8:01 null 3 8:02 null 4 8:12 null T2: id time2 2 8:09 4 8:15 What I want is: T1: id time1 time2 1 8:00 null 2 8:01 8:09 3 8:02 null 4 8:12 8:15 Is there an sql command sequence that can achieve this? Its easy to think about it iteratively, but in the absense of explicit iterators in sql (to my understanding) I hope that there's another way. Its clear that I can exec the join: select T1.id, T1.time2 from T1, T2 where T1.id = T2.id but using this as part of an update is broken (well, because I don't know how to do it): (wrong:) update T1 set time2 = ( select time2 from T1, T2 where T1.id = T2.id) where id = ( select id from T1, T2 where T1.id = T2.id ) how can I write an on T1.time2 that matches T1.id = T2.id and updates the T1.time2 null with the T2.time2 ?
-
I have 2 tables. I want to use info from the second table to update column values in the first. Consider: T1: id time1 time2 1 8:00 null 2 8:01 null 3 8:02 null 4 8:12 null T2: id time2 2 8:09 4 8:15 What I want is: T1: id time1 time2 1 8:00 null 2 8:01 8:09 3 8:02 null 4 8:12 8:15 Is there an sql command sequence that can achieve this? Its easy to think about it iteratively, but in the absense of explicit iterators in sql (to my understanding) I hope that there's another way. Its clear that I can exec the join: select T1.id, T1.time2 from T1, T2 where T1.id = T2.id but using this as part of an update is broken (well, because I don't know how to do it): (wrong:) update T1 set time2 = ( select time2 from T1, T2 where T1.id = T2.id) where id = ( select id from T1, T2 where T1.id = T2.id ) how can I write an on T1.time2 that matches T1.id = T2.id and updates the T1.time2 null with the T2.time2 ?
UPDATE T1
SET time2 = T2.time2
FROM T2
WHERE T1.id = T2.idTested with SQL Server 2000 SP4. You basically have an implicit cross-join with the table you're updating and the tables and join statements listed in the FROM clause.
Stability. What an interesting concept. -- Chris Maunder