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_acces

Solution 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.

Solution 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

Solution 3:

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;

Solution 4:

INSERTINTO 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