SQL: Move column data to other table in same relation
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
Table 2 : account_users - target columns: oauth_token_key, oauth_token_secret
The relation key between these tables is "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.
Thanks in advance.
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 JOIN
syntax on MySQL Update.
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
Here is a documentation on VALUES()
: http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_values
Since the title is SQL, and not DB specific... here's a solution for those who stumble upon this while searching for 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;
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
精彩评论