Hi, hope somebody can help me - what I want to do is simple I'm sure, but I can't work out how to do it: I want to replace a column in a table, containing text, with a key to another new table which holds the text.
Example: Given a table, 'people', which contains two columns, name, and town, like this: name town ------ ------ fred london jo liverpool amy chicago mary chicago I create a new table, towns: townid town ------ ---- 1 london 2 liverpool 3 chicago So far good. Now I want to add a townid column to 'people', and update to point to the corresponding row in 'towns'. I add a new column, townid, but then how do I update the values?? I have tried both of these: UPDATE people SET people.townid = towns.townid WHERE people.town=towns.town; and UPDATE people INNER JOIN towns ON people.town = towns.town SET people.townid = towns.townid; mysql gives syntax errors in both cases (both work ok with access 2000). Is there a way to do this without creating temporary intermediate tables? Any help greatly appreciated! --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
