MySQL将数据从一个表移动到另一个表，匹配ID

I have (a.o.) two MySQL tables with (a.o.) the following columns:

我有(a.o.)两个MySQL表(a.o.)以下列:

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表,用一个与users.id匹配的整数替换tweets.from_user。其次,我想填写相应的users.from_user_id,最后,我想删除tweets.from_user_id,以便最终结果如下:

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 :-)

任何帮助将不胜感激 :-)

2 个解决方案

#1

I think something like this should work:

我认为这样的事情应该有效:

UPDATE FROM tweets t 
LEFT JOIN users u ON u.from_user = t.from_user
SET t.from_user = u.id

UPDATE FROM users u
LEFT JOIN tweets t ON t.from_user = u.from_user
SET u.from_user_id = t.from_user_id

ALTER TABLE tweets DROP COLUMN from_user_id

#2

Example Work:

Tweet Table

http://data.stackexchange.com/*/s/2144/mysql-move-data-from-one-table-to-another-matching-ids

Users Table

http://data.stackexchange.com/*/qe/2145/mysql-move-data-from-one-table-to-another-matching-ids

UPDATE users
SET users.from_user_id = tweets.from_user_id
FROM users
LEFT JOIN tweets ON users.from_user = tweets.from_user;

UPDATE tweets
SET tweets.from_user = users.id
FROM tweets
LEFT JOIN users ON tweets.from_user = users.from_user;

ALTER TABLE tweets DROP COLUMN from_user_id;
ALTER TABLE tweets ALTER COLUMN from_user int;

Possible MySQL Query

NOT TESTED

UPDATE users, tweets
SET users.from_user_id = tweets.from_user_id
WHERE users.from_user = tweets.from_user;

UPDATE tweets, users
SET tweets.from_user = users.id
WHERE tweets.from_user = users.from_user;

ALTER TABLE tweets DROP COLUMN from_user_id;
ALTER TABLE tweets CHANGE COLUMN from_user from_user int;