2wsxdr5 <[EMAIL PROTECTED]> wrote on 01/11/2006 12:30:10 PM: > I have two tables with data on people in them. Table A is a subset of > table B, However, there is data about these people in table B that is > not in table A. with a simple select I can do a join and get a result > set with all the data I need to show, but what I would like to do is > change table A so it also has one of the fields from table B. Adding > the field to table A is trivial , but how do I then populate that new > field with data from the table B? > > -- > Chris W > KE5GIX > > Gift Giving Made Easy > Get the gifts you want & > give the gifts they want > One stop wish list for any gift, > from anywhere, for any occasion! > http://thewishzone.com >
Assuming you are using a recent version of MySQL, you have the ability to do a multi-table update. It looks just like a multi-table select with a few pieces rearranged UPDATE TABLEA a LEFT JOIN TABLEB b ON b.some_value = a.some_value SET a.new_field = b.field_with_other_data; The FROM clause becomes the UPDATE clause (complete with joins). The SELECT clause transforms itself into the SET clause. I didn't need a WHERE clause because the ON conditions took care of that this time. Shawn Green Database Administrator Unimin Corporation - Spruce Pine