SQL: Move column data to other table in same relation

Question

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.

Answer 1

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.

Answer 2

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