MySQL SQL：将列数据移动到具有相同关系的其他表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6856849/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: Move column data to other table in same relation
提问by byteshift
I'm wondering if it's possible to move all data from one column in table to another table. Here's what i'm trying to do:
我想知道是否可以将所有数据从表中的一列移动到另一张表。这是我想要做的：
Table 1 : users - columns trying to read+move = oauth_access_key and oauth_access_secret
表 1：用户 - 尝试读取+移动的列 = oauth_access_key 和 oauth_access_secret
Table 2 : account_users - target columns: oauth_token_key, oauth_token_secret
表 2：account_users - 目标列：oauth_token_key、oauth_token_secret
The relation key between these tables is "user_id".
这些表之间的关系键是“user_id”。
Is this possible in one query? I know this is easily done in PHP, but i'm wondering if this can be done in plain SQL.
这在一个查询中是可能的吗？我知道这在 PHP 中很容易完成，但我想知道这是否可以用普通的 SQL 完成。
Thanks in advance.
提前致谢。
回答by Jacob
UPDATE users, account_users
SET account_users.oauth_token_key=users.oauth_access_key,
account_users.oauth_token_secret = users.oauth_access_secret
WHERE account_users.user_id=users.user_id;
You can use JOINsyntax on MySQL Update.
您可以JOIN在MySQL Update上使用语法。
回答by Mike
I think the answer you are looking for is
我想你正在寻找的答案是
INSERT INTO `account_users` (`user_id`, `oauth_token_key`, `oauth_token_secret`)
SELECT `user_id`, `oauth_access_key`, `oauth_access_secret` FROM `user`
ON DUPLICATE KEY UPDATE
`oauth_token_key` = VALUES(`oauth_token_key`),
`oauth_token_secret` = VALUES(`oauth_token_secret`);
EDIT
编辑
I am assuming you want to move data as in 'put it somewhere it hasn't been yet'.
我假设您想移动数据，就像“将它放在尚未移动的地方”一样。
EDIT2
编辑2
Here is a documentation on VALUES(): http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_values
这里是一个文档VALUES()：http: //dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_values
回答by Neal
Since the title is SQL, and not DB specific... here's a solution for those who stumble upon this while searching for Postgres:
由于标题是 SQL，而不是特定于 DB 的......这里是那些在搜索Postgres时偶然发现的解决方案：
UPDATE account_users
SET oauth_token_key = users.oauth_access_key,
oauth_token_secret = users.oauth_access_secret
FROM profiles
WHERE account_users.user_id = users.user_id;
回答by PtPazuzu
INSERT INTO account_users (user_id, oauth_token_secret)
SELECT users.user_id, users.oauth_access_secret FROM users
ON DUPLICATE KEY UPDATE account_users.oauth_token_secret = users.oauth_access_secret
