MySQL move data from one table to another, matching ID's
- by Reveller
I have (a.o.) two MySQL tables with (a.o.) the following columns:
tweets:
-------------------------------------
id text from_user_id from_user
-------------------------------------
1 Cool tweet! 13295354 tradeny
2 Tweeeeeeeet 43232544 bolleke
3 Yet another 13295354 tradeny
4 Something.. 53546443 janusz4
users:
-------------------------------------
id from_user num_tweets from_user_id
-------------------------------------
1 tradeny 2235
2 bolleke 432
3 janusz4 5354
I now want to normalize the tweets table, replacing tweets.from_user with an integer that matches users.id. Secondly, I want to fill in the corresponding users.from_user_id, Finally, I want to delete tweets.from_user_id so that the end result would look like:
tweets:
------------------------
id text from_user
------------------------
1 Cool tweet! 1
2 Tweeeeeeeet 2
3 Yet another 1
4 Something.. 3
users:
-------------------------------------
id from_user num_tweets from_user_id
-------------------------------------
1 tradeny 2235 13295354
2 bolleke 432 43232544
3 janusz4 5354 53546443
My question is whether one could help me form the proper queries for this. I have only come so far:
UPDATE tweets SET from_user =
(SELECT id FROM users WHERE from_user = tweets.from_user)
WHERE...
UPDATE users SET from_user_id =
(SELECT from_user_id FROM tweets WHERE from_user = tweets.from_user)
WHERE...
ALTER TABLE tweets DROP from_user_id
Any help would be greatly appreciated :-)
